Advanced SQL

Tags: #◇Code Snippets #◇CCINFOM #◇SQL #◇Joins #◇Subqueries #◇Union #◇M4

◇Overview

Advanced SQL focuses on querying across multiple tables, using JOIN operations, combining query results with UNION, and nesting queries (Subqueries).

◇Syntax Breakdown

JOINS

Joins combine data from different tables based on related columns.

1. INNER JOIN

Only includes rows where there is a match in both tables based on the join condition.

SELECT T1.PROD_CODE, T1.DESCRIPTION, T2.QTY
FROM TABLE1 T1
INNER JOIN TABLE2 T2 
ON T1.PROD_CODE = T2.PROD_CODE;

-- Alternative syntax if column names match exactly:
SELECT *
FROM TABLE1 
INNER JOIN TABLE2 USING (PROD_CODE);

2. LEFT (OUTER) JOIN

Returns all rows from the left table. If there is no matching row in the right table, NULL values are filled in for the right table's columns.

SELECT c.customerNumber, c.customerName, o.orderNumber, o.status
FROM customers c
LEFT JOIN orders o 
ON c.customerNumber = o.customerNumber;

Condition placement (WHERE vs ON):
In a LEFT JOIN, putting a condition in the ON clause acts differently than putting it in the WHERE clause.
In ON: It restricts the matching process (unmatched left-table rows still appear with NULLs).
In WHERE: It filters the final result set (effectively turning the query into an inner join if filtering a right-table column).
(Note: For an INNER JOIN, placing conditions in ON or WHERE is logically equivalent).

UNION

Combines the result sets of two or more SELECT statements vertically. Both tables must have the same number of columns, same column names, and compatible data types.

-- Find items bought for themselves OR as gifts
SELECT CUST_NO, PROD_CODE, QTY
FROM BOUGHT_FOR_SELF
UNION
SELECT CUST_NO, PROD_CODE, QTY
FROM BOUGHT_AS_GIFT;

Subqueries

A query nested inside another query (Select within a Select).

-- Find customers who bought product T0001
SELECT * 
FROM CUSTOMERS 
WHERE CUST_NO IN (
    SELECT CUST_NO 
    FROM ORDERS 
    WHERE PROD_CODE = 'T0001'
);

[!tip] Thinking Discipline for SQL

1. FROM: Identify where the data comes from.
2. WHERE/ON: Identify filtering and joining conditions.
3. SELECT: Pick the fields required.
4. ORDER BY: Sort the output as needed.

◇References & Navigation

• Previous Topic: ◇Introduction to SQL
• Next Topic: ◇Modeling Data
• Related Notes: ◇Introduction to Databases