The Power of Joins

In a normalized database, data is split across multiple tables to eliminate redundancy. Joins allow you to stitch this scattered data back together by matching rows from two or more tables based on a related column (usually a Foreign Key).

1. Inner Join

An INNER JOIN is strict. It returns ONLY the records that have matching values in BOTH tables. If a customer has no orders, they will not appear in the final result.

sql

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers 
  ON Orders.CustomerID = Customers.CustomerID;

2. Outer Joins (Left, Right, Full)

Unlike an Inner Join, an Outer Join preserves unmatched records from one or both tables by padding the missing columns with NULL.

LEFT JOIN: Returns ALL records from the left table, regardless of matches. Unmatched right-side data becomes NULL.
RIGHT JOIN: Returns ALL records from the right table, regardless of matches. Unmatched left-side data becomes NULL.
FULL OUTER JOIN: Returns ALL records from BOTH tables. If there is no match on either side, it pads with NULLs.

sql

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders 
  ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

Visualizing a Left Join

Customers (Left)

ID	Name
1	Alice
2	Bob

Orders (Right)

OrderID	CustID
O1	1
O2	1

LEFT JOIN Result

Name	OrderID
Alice	O1
Alice	O2
Bob	NULL

Notice how Bob is still included in the result even though he has no orders. The database simply padded his OrderID with a NULL value.

UNION vs UNION ALL

While JOINS combine columns horizontally, UNION combines result sets vertically (stacking rows on top of each other). To use UNION, both queries must return the same number of columns with compatible data types.

- UNION automatically removes duplicate rows. - UNION ALL retains all duplicates and is much faster computationally.

sql

SELECT City 
FROM Customers

UNION

SELECT City 
FROM Suppliers
ORDER BY City;

Grouping Data (GROUP BY & HAVING)

The GROUP BY clause groups rows with identical values into summary rows. It is almost always used alongside aggregate functions like COUNT() or SUM().

Placement Trap: WHERE vs HAVING

Interviewers frequently ask for the difference between WHERE and HAVING. WHERE filters individual rows BEFORE they are grouped. HAVING filters aggregate values AFTER the rows have been grouped together. You CANNOT use aggregate functions (like COUNT() > 5) inside a WHERE clause!

sql

SELECT Country, COUNT(CustomerID) AS TotalCustomers
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY TotalCustomers DESC;

Schema Modifications (DDL)

Data Definition Language (DDL) commands are strictly used by database administrators to create, modify, and drop physical database schemas and structures.

sql

/* Table Creation */
CREATE TABLE Customers (
    CustomerID int,
    CustomerName varchar(255),
    City varchar(100),
    Country varchar(100)
);

/* Schema Modifications using ALTER */
ALTER TABLE Customers 
  ADD Email varchar(255);
  
ALTER TABLE Customers 
  DROP COLUMN Email;
  
/* Table Destruction */
DROP TABLE Customers;

/* Table Truncation (Deletes data but keeps schema) */
TRUNCATE TABLE Customers;

Summary of DDL Structural Effects

DDL Command	Affected Schema Element	Structural Modification Action
CREATE TABLE	Logical Relation Schema	Allocates disk space and registers table metadata.
DROP TABLE	Logical & Physical Structure	Deletes table metadata and deallocates disk space.
TRUNCATE TABLE	Table Storage Blocks	Deletes all records and frees storage while keeping schema.
ALTER TABLE ADD	Relation Column Schema	Appends a new column to an existing table schema.
ALTER TABLE DROP	Relation Column Schema	Removes a column and its associated data from the schema.
ALTER TABLE MODIFY	Attribute Data Domain	Updates an existing column's data type or length.

Fill in the Blank

Sort the Concepts

Sort the following commands into their correct SQL Sub-Language:

DDL (Schema Modifiers)

DML (Data Manipulators)

Unsorted Items:

ALTER TABLE

TRUNCATE

INNER JOIN

UPDATE

DROP TABLE

The Power of Joins
1. Inner Join
2. Outer Joins (Left, Right, Full)
Visualizing a Left Join
UNION vs UNION ALL
Grouping Data (GROUP BY & HAVING)
Schema Modifications (DDL)
Summary of DDL Structural Effects