ADVANCED SQL
Techniques
Baraa Khatib Salkini
YouTube | DATA WITH BARAA
SQL Course | Challenges & Solutions of Complex SQL Projects
SQL Course | Database Architecture
Subquery
Baraa Khatib Salkini
YouTube | DATA WITH BARAA
SQL Course | Subquery
How
Subquery
Works
SQL Course | Subquery
SUBQUERY
SQL Course | Subquery
SQL Course | Subquery
SQL Course | Subquery Result Types
SQL Course | Subquery | How DB Execute Subquery
Subquery in
FROM Clause
Main Query
SELECT column1, column2,…
FROM ( SELECT column FROM table1 WHERE condition )AS alias
Subquery
SQL Course | Subquery |FROM
Subquery in
FROM Clause
Subquery in
SELECT Clause
Main Query
SELECT
Column1,
( SELECT column FROM table1 WHERE condition ) AS alias
FROM table1
Subquery
Rules
Only Scalar Subqueries are allowed to be used
SQL Course | Subquery|SELECT
Subquery in
SELECT
Clause
Subquery in
WHERE Clause
Comparison Operators
Main Query
SELECT column1, column2,…
FROM table1
WHERE column =( SELECT column FROM table2 WHERE condition )
Rules Subquery
Only Scalar Subqueries are allowed to be used
SQL Course | Subquery|WHERE Comparison
Subquery in
WHERE Clause
Comparison Operators
= Equal WHERE Sales = (SELECT AVG(Sales) FROM ORDERS)
!= <> Not Equal WHERE Sales != (SELECT AVG(Sales) FROM ORDERS)
> Greater than WHERE Sales > (SELECT AVG(Sales) FROM ORDERS)
< Less than WHERE Sales < (SELECT AVG(Sales) FROM ORDERS)
>= Greater than or equal to WHERE Sales >= (SELECT AVG(Sales) FROM ORDERS)
<= Less than or equal to WHERE Sales <= (SELECT AVG(Sales) FROM ORDERS)
SQL Course | Subquery|WHERE Comparison
Comparison
Operators
Subquery in
WHERE Clause
Comparison Operators
Subquery in
WHERE Clause
In Operator
Main Query
SELECT column1, column2,…
FROM table1
WHERE column IN ( SELECT column FROM table2 WHERE condition )
Subquery
SQL Course | Subquery|WHERE IN
Logical
Operators
IN Checks if a value matches any value in a list WHERE Sales IN (SELECT …)
NOT IN Checks if a value does not matches any value in a list WHERE Sales NOT IN (SELECT …)
EXISTS Checks if subquery returns any rows WHERE EXISTS (SELECT …)
NOT EXISTS Checks if subquery returns no rows WHERE NOT EXISTS (SELECT …)
ANY Returns true if a value matches any value in a list. WHERE Sales < ANY (SELECT …)
ALL Returns true if a value matches all values in a list. WHERE Sales > ALL (SELECT …)
Subquery in
WHERE Clause
In Operator
Subquery in
WHERE Clause
ALL Operators
Main Query
SELECT column1, column2,…
FROM table1
WHERE column < ALL( SELECT column FROM table1 WHERE condition )
Subquery
SQL Course | Subquery|WHERE ALL
Subquery in
WHERE Clause
ANY Operator
Main Query
SELECT column1, column2,…
FROM table1
WHERE column < ANY( SELECT column FROM table1 WHERE condition )
Subquery
SQL Course | Subquery|WHERE ANY
SQL Course | Subquery|Correlated
Non-Correlated Subquery Correlated Subquery
Definition Subquery is independent of the main query Subquery is dependent of the main query
Executed once and its result is used by the main query Executed for each row processed by the main query
Execution
Can be executed on its Own Can't be executed on its Own.
Easy to use Easier to read Harder to read and more complex
Performance Executed only once leads to better Performance Executed multiple times leads to bad Performance
Usage Static Comparisons, Filtering with Constants Row-by-Row Comparisons, Dynamic Filtering
SQL Course | Subquery|Correlated
Correlated Subquery in
WHERE Clause
EXISTS Operator Main Query
SELECT column1, column2,…
FROM Table2
WHERE EXISTS ( SELECT 1
FROM Table1
WHERE Table1.ID = Table2.ID
)
Subquery
SQL Course | Subquery|Correlated
How EXISTS Works?
For each row in
Runs outer
Main query
Query
Run outer
Runs Subquery
query
No Result? returns Value ?
Row of Main Query Row of Main Query
Runs outer query Runs outer query
is excluded is included
SQL Course | Subquery|Correlated
JOINS SUBQUERIES
SELECT *
SELECT o.*
FROM Orders
FROM Orders o
WHERE CustomerID IN
Syntax JOIN Customers c
(SELECT CustomerID
ON c.CustomerID = o.CustomerID
FROM Customers
AND c.Country = 'USA'
WHERE Country = 'USA')
Readability Not easy to read & maintain easy to read & maintain
Performance Fast Slow
Duplicate May lead to duplicate Safer
no risk to have duplicates
Best
Practices
Useful with Larg tables Useful with small tables
SQL Course | Subquery vs Joins
CTE
Common Table Expression
Baraa Khatib Salkini
YouTube | DATA WITH BARAA
SQL Course | CTE
SQL Course | CTE vs Subquery
SQL Course | CTE vs Subquery
SQL Course | CTE
SQL Course | CTE
SQL Course | CTE | How DB Execute CTE
SQL Course | CTE Types
SQL Course | Standalone CTE
Standalone CTE
SQL Course | Standalone CTE
Standalone CTE
WITH CTE-Name AS
(
SELECT … CTE Query
FROM … - CTE Definition -
WHERE …
)
SELECT …
FROM CTE-Name Main Query
- CTE Usage -
WHERE …
SQL Course | CTE Syntax
Multiple CTEs
SQL Course | Multiple CTE
Multiple CTEs
WITH CTE-Name1 AS
(
CTE Query
- CTE Definition - SELECT …
FROM …
WHERE …
)
, CTE-Name2 AS
(
CTE Query
SELECT …
- CTE Definition - FROM …
WHERE …
)
SELECT …
Main Query FROM CTE-Name1
- CTE Usage - JOIN CTE-Name2
WHERE …
SQL Course | Multiple CTE
SQL Course | Nested CTE
Nested CTEs
SQL Course | Nested CTE
Nested CTEs
WITH CTE-Name1 AS
(
SELECT … CTE Query
Standalone CTE - CTE Definition -
FROM …
WHERE …
)
, CTE-Name2 AS
(
NESTED CTE SELECT … CTE Query
- CTE Definition -
FROM CTE-Name1
WHERE …
)
SELECT …
Main Query
FROM CTE-Name2 - CTE Usage -
WHERE …
SQL Course | Nested CTE
SQL Course | Recursive CTE
Recursive CTE
SQL Course | Recursive CTE
Recursive CTE
WITH CTE-Name AS
(
SELECT …
Anchor
FROM …
Query
WHERE …
CTE Query
- CTE Definition - UNION ALL
SELECT …
Recursive
FROM CTE-Name Query
WHERE [Break Condition]
)
SELECT …
Main Query FROM CTE-Name
- CTE Usage -
WHERE …
SQL Course | Recursive CTE
SQL Course | Recursive CTE
SQL Course | Recursive CTE
SQL Course | Recursive CTE
SQL Course | CTE
Views
Database Object
Baraa Khatib Salkini
YouTube | DATA WITH BARAA
SQL Course | Views
SQL Course | Views | Database Structure
SQL Course | Views | 3 Layers Architecture of Database
SQL Course | Views
SQL Course | Views vs Tables
SQL Course | Views vs Tables
SQL Course | Views | Central Logic
SQL Course | Views | Central Logic
SQL Course | Views vs CTE
SQL Course | Views vs CTE
VIEWS
DDL
Statement
CREATE VIEW VIEW-NAME AS
(
SELECT …
FROM …
Query WHERE …
)
SQL Course | Views
Flexibility & Dynamic
SQL Course | Views | Flexibility & Dynamic
Hide Complexity
SQL Course | Views | Hide Complexity
Security
SQL Course | Views | Security
Multiple Languages
SQL Course | Views | Multiple Languages
Virtual Data Marts
SQL Course | Views | Virtual Data Marts
CTAS
Create Table As SELECT
Baraa Khatib Salkini
YouTube | DATA WITH BARAA
SQL Course | CTAS
SQL Course | CTAS | What are Tables
SQL Course | CTAS | What are Tables
SQL Course | CTAS | What are Tables
SQL Course | CTAS | Table Types
SQL Course | CTAS vs CREATE/INSERT
SQL Course | CTAS | Table vs Views
CREATE / INSERT
syntax
DDL CREATE TABLE Table-Name
Statement (
ID INT,
Name VARCHAR (50)
)
INSERT INTO Table-Name
Insert VALUES (1, ’Frank’)
Statement
SQL Course | CTAS | Create/INSERT Syntax
CTAS
syntax
DDL
Statement
CREATE TABLE NAME AS
SELECT …
(
SELECT … INTO New-Table
FROM …
Query FROM …
WHERE …
WHERE …
)
MySQL | Postgres | Oracle Sql Server
SQL Course | CTAS Syntax
Optimize Performance
SQL Course | CTAS | Optimize Performance
Create Snapshot
You want to preserve
the current state of
data before
performing operations
that might change it.
SQL Course | CTAS | Creating Snapshot
Physical Data Marts
SQL Course | CTAS | Physical Data Marts
TEMPORARY
TABLE
SELECT …
INTO # New-Table
FROM …
WHERE …
Sql Server
SQL Course | TEMP Tables
PERMENANT TEMPORARY
CREATE TABLE TABLE
CREATE TABLE TABLE-NAME AS CREATE TEMPORARY TABLE TABLE-NAME AS
( (
SELECT … SELECT …
FROM … FROM …
WHERE … WHERE …
) )
MySQL | Postgres | Oracle
SQL Course | TEMP Tables
SQL Course | TEMP Tables
SQL Course | TEMP Tables
Empty Permenant Temporary
View
Table Table Table
CREATE VIEW View-Name AS SELECT … SELECT …
CREATE TABLE Table-Name
(
( INTO New-Table INTO #New-Table
SELECT …
ID INT,
FROM …
Name VARCHAR (50) FROM … FROM …
WHERE …
) WHERE … WHERE …
)
SQL Course | DB Objects
Big Picture
All Techniques
Baraa Khatib Salkini
YouTube | DATA WITH BARAA
SQL Course | Comparison
Stored Procedure
Baraa Khatib Salkini
YouTube | DATA WITH BARAA
SQL Course | Stored Procedure
Stored Procedure
SQL Course | Stored Procedure
SQL Course | Stored Procedure vs Query
Stored Procedure vs Python
SQL Course | Stored Procedure vs Python
Stored Procedure
CREATE PROCEDURE ProcedureName AS
BEGIN
Stored Procedure
Definition
-- SQL STATEMENTS GO HERE
END
Stored Procedure
Execution (Call) EXEC ProcedureName
SQL Course | Stored Procedure | Syntax
Error Handling
Start
BEGIN TRY
Execute
-- SQL statements that might cause an error
TRY
END TRY
Error
Error?
BEGIN CATCH
-- SQL statements To Handle The Error Execute
No CATCH
END CATCH Error
End
SQL Course | Stored Procedure | Error Handling
Flow Control
Start
Value is Yes
NULL?
Update
No To Zero
End
SQL Course | Stored Procedure | Flow Control
Triggers
Baraa Khatib Salkini
YouTube | DATA WITH BARAA
SQL Course | Triggers
Triggers
SQL Course | Triggers
SQL Course | Trigger Types
Maintaining Logs
SQL Course | Triggers | Maintaining Logs
Triggers
CREATE TRIGGER TriggerName ON TableName
WHEN AFTER INSERT, UPDATE, DELETE
BEGIN
WHAT -- SQL STATEMENTS GO HERE
END
SQL Course | Triggers | Syntax
