SQL Advanced Queries: A Deep Dive

Advanced queries allow for complex data manipulation, helping you analyze data in ways that go beyond basic SQL statements. Key operators and subquery types include UNION, INTERSECT, EXCEPT, EXISTS, ANY, ALL, and correlated and non-correlated subqueries.

Advanced Queries are:

1. UNION and UNION ALL

• UNION combines the results of two or more SELECT statements, removing duplicates.
• UNION ALL combines all results, including duplicates.

Syntax (UNION and UNION ALL):

Union:

SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2;

Union All:

SELECT column1, column2
FROM table1
UNION ALL
SELECT column1, column2
FROM table2;

Example: Using two tables, Employees_US and Employees_UK:

Employees_US Table:

EmployeeID	Name	Country
1	Alice	USA
2	Bob	USA

Employees_UK Table:

EmployeeID	Name	Country
1	Charlie	UK
2	Alice	UK

Query 1:

SELECT Name, Country FROM Employees_US
UNION
SELECT Name, Country FROM Employees_UK;

Output (UNION):

Name	Country
Alice	USA
Bob	USA
Charlie	UK

Query 2:

SELECT Name, Country FROM Employees_US
UNION ALL
SELECT Name, Country FROM Employees_UK;

Output (UNION ALL):

Name	Country
Alice	USA
Bob	USA
Charlie	UK
Alice	UK

2. INTERSECT and EXCEPT

• INTERSECT returns only records found in both result sets.
• EXCEPT (or MINUS in Oracle) returns records from the first result set that are not in the second.

Syntax (INTERSECT and EXCEPT):

INTERSECT

SELECT column1, column2
FROM table1
INTERSECT
SELECT column1, column2
FROM table2;

EXCEPT

SELECT column1, column2
FROM table1
EXCEPT
SELECT column1, column2
FROM table2;

Example (SQL Server and PostgreSQL only, not supported in MySQL):

Employees Table:

EmployeeID	Name	Country
1	Alice	USA
2	Bob	USA
3	Charlie	UK

Employees_Overseas Table:

EmployeeID	Name	Country
1	Alice	UK
2	Charlie	UK
3	David	CANADA

Query (INTERSECT):

SELECT Name, Country FROM Employees
INTERSECT
SELECT Name, Country FROM Employees_Overseas;

Output (INTERSECT):

Name	Country
Alice	UK
Charlie	UK

Query (EXCEPT):

SELECT Name, Country FROM Employees
EXCEPT
SELECT Name, Country FROM Employees_Overseas;

Output (EXCEPT):

Name	Country
Bob	USA

3. EXISTS and NOT EXISTS

• EXISTS checks if a subquery returns any rows.
• NOT EXISTS checks if a subquery returns no rows.

Syntax (EXISTS and NOT EXISTS):

EXISTS

SELECT column1, column2
FROM table
WHERE EXISTS (SELECT 1 FROM other_table WHERE condition);

NOT EXISTS

SELECT column1, column2
FROM table
WHERE NOT EXISTS (SELECT 1 FROM other_table WHERE condition);

Example:

Employees Table:

EmployeeID	Name	DepartmentID
1	Alice	1
2	Bob	2
3	Charlie	3

Departments Table:

DepartmentID	DepartmentName
1	HR
2	IT

Query:

SELECT Name FROM Employees
WHERE EXISTS (SELECT 1 FROM Departments WHERE Employees.DepartmentID = Departments.DepartmentID);

Output:

Name

Alice

Bob

4. ANY and ALL

• ANY returns true if any value in a subquery meets the condition.
• ALL returns true only if all values meet the condition.

Syntax (ANY and ALL):

ANY

SELECT column1
FROM table
WHERE column2 > ANY (SELECT column2 FROM other_table);

ALL

SELECT column1
FROM table
WHERE column2 > ALL (SELECT column2 FROM other_table);

Example:

Sales Table:

SaleID	Amount
1	500
2	1500
3	3000

Query (ANY):

SELECT Amount FROM Sales WHERE Amount > ANY (SELECT Amount FROM Sales WHERE Amount < 3000);

Output (ANY):

Amount

1500

3000

Query (ALL):

SELECT Amount FROM Sales WHERE Amount > ALL (SELECT Amount FROM Sales WHERE Amount < 3000);

Output (ALL):

Amount

3000

5. Subqueries (Correlated and Non-correlated)

• Non-correlated Subquery: Independent subquery that can be executed alone.
• Correlated Subquery: Subquery that references the outer query and is evaluated once per row.

Syntax (Correlated and Non-correlated):

Non-correlated

SELECT column1
FROM table1
WHERE column2 IN (SELECT column3 FROM table2 WHERE condition);

Correlated

SELECT column1
FROM table1 AS a
WHERE column2 = (SELECT MAX(column3) FROM table2 AS b WHERE a.common_field = b.common_field);

Example:

Employees Table:

EmployeeID	Name	Salary
1	Alice	5000
2	Bob	6000
3	Carol	7000

Departments Table:

DepartmentID	DepartmentName	MaxSalary
1	HR	6000
2	IT	7000

Query (Non-correlated):

SELECT Name FROM Employees
WHERE Salary > (SELECT MAX(Salary) FROM Employees WHERE DepartmentID = 1);

Output (Non-correlated):

Name

Carol

Query (Correlated):

SELECT Name, Salary FROM Employees AS e
WHERE Salary = (SELECT MAX(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID);

Conclusion

Advanced queries and subqueries enhance the flexibility of SQL, allowing you to handle complex data scenarios across various database types. Each operator and subquery type has unique characteristics, and knowing when to use them is crucial for data manipulation.