Subqueries and Complex Queries

SQL (Structured Query Language) is the standard language used for managing and querying relational databases. One of the most powerful features of SQL is the ability to use subqueries and complex query structures to filter, analyze, and manipulate data more effectively.

1. What Are Subqueries?

Subqueries are nested queries that are embedded inside another SQL query. They can return a single value or a dataset and are used within SELECT, INSERT, UPDATE, or DELETE statements.

Subqueries are commonly used in the following clauses:

• WHERE
• FROM (as a derived table)
• HAVING
• SELECT (for inline calculations)

1.1 Subqueries in WHERE Clause

SELECT * FROM Customers 
WHERE ID IN (SELECT CustomerID FROM Orders WHERE OrderDate > '2024-01-01');

This query returns all customers who placed orders after January 1, 2024.

1.2 Subqueries in FROM Clause (Derived Tables)

SELECT City, AvgAge FROM 
    (SELECT City, AVG(Age) AS AvgAge FROM Customers GROUP BY City) AS CityAges;

This query calculates the average age of customers in each city using a subquery as a virtual table.

2. Using JOINs to Combine Tables

2.1 INNER JOIN

SELECT Customers.Name, Orders.OrderDate 
FROM Customers 
INNER JOIN Orders ON Customers.ID = Orders.CustomerID;

This query lists customers who have placed at least one order.

2.2 LEFT JOIN

SELECT Customers.Name, Orders.OrderDate 
FROM Customers 
LEFT JOIN Orders ON Customers.ID = Orders.CustomerID;

This query lists all customers, along with their order dates if available.

2.3 RIGHT JOIN

SELECT Customers.Name, Orders.OrderDate 
FROM Customers 
RIGHT JOIN Orders ON Customers.ID = Orders.CustomerID;

This query lists all orders, including those that may not be linked to a customer (if allowed by the schema).

2.4 FULL OUTER JOIN

SELECT Customers.Name, Orders.OrderDate 
FROM Customers 
FULL OUTER JOIN Orders ON Customers.ID = Orders.CustomerID;

This query includes all records from both Customers and Orders tables, matching where possible.

2.5 CROSS JOIN

SELECT Customers.Name, Products.ProductName 
FROM Customers 
CROSS JOIN Products;

This query produces a Cartesian product of Customers and Products.

3. Complex Queries

3.1 Using UNION to Combine Query Results

SELECT Name, Surname FROM Customers WHERE City = 'Istanbul'
UNION
SELECT Name, Surname FROM Customers WHERE City = 'Ankara';

This query merges customers from Istanbul and Ankara into one result set, removing duplicates.

3.2 Using CASE for Conditional Logic

SELECT Name, Surname, Age, 
    CASE 
        WHEN Age < 18 THEN 'Child'
        WHEN Age BETWEEN 18 AND 65 THEN 'Adult'
        ELSE 'Senior'
    END AS AgeGroup
FROM Customers;

This query classifies customers based on their age into different age groups.

Subqueries and complex SQL queries empower developers and analysts to extract valuable insights from data. Using clauses like WHERE, FROM, HAVING, and SELECT with subqueries, and combining data using JOIN, UNION, CASE, and CTEs can help handle advanced scenarios in data manipulation and reporting.