Class Notes: Table Joins in SQL

Introduction to Joins

• A join combines rows from two or more tables
• Multiple tables are listed in the FROM clause
• Types of joins in MySQL:
  1. CROSS JOIN (Cartesian Product)
  2. EQUI-JOIN
  3. NATURAL JOIN
  4. LEFT JOIN
  5. RIGHT JOIN

Cross Join (Cartesian Product)

• Returns all possible combinations of rows from both tables
• Each row from first table is joined with all rows of second table
• Formula: Total rows = rows in first table × rows in second table

Example:

SELECT * FROM Shades, Color;
-- or
SELECT * FROM Shades CROSS JOIN Color;

Output Table Example:

shade_id	shade_name	color_id	color_name
1	Light	1	Red
1	Light	2	Green
1	Light	3	Blue
2	Dark	1	Red
…	…	…	…

Equi-Join

• Columns are compared for equality
• Common column appears twice in output
• Uses WHERE clause with equality condition

Example:

SELECT * FROM emp, dept 
WHERE emp.deptno = dept.deptno;

Using Table Aliases:

SELECT e.empno, e.name, d.dname 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

Output Table Example:

empno	name	deptno	dname	deptno	location
101	Alam	10	Sales	10	Delhi
102	Ravi	20	HR	20	Mumbai

Natural Join

• Similar to Equi-Join but eliminates duplicate columns
• Only one copy of common column appears

Example:

SELECT empno, name, dname, location 
FROM emp NATURAL JOIN dept;

Handling Ambiguous Columns:

SELECT emp.empno, emp.name, dept.dname 
FROM emp NATURAL JOIN dept;

Additional Conditions in Joins

SELECT e.empno, e.name, d.dname 
FROM emp e JOIN dept d ON e.deptno = d.deptno 
WHERE e.salary > 50000;

LEFT JOIN

• Returns all rows from left table (first table)
• Returns NULL for non-matching rows from right table

Example:

SELECT e.empno, e.name, p.pname 
FROM emp e LEFT JOIN project p ON e.empno = p.empno;

Output Table Example:

empno	name	pname
101	Alam	Website
102	Ravi	Database
103	Priya	NULL

RIGHT JOIN

• Returns all rows from right table (second table)
• Returns NULL for non-matching rows from left table

Example:

SELECT p.pid, p.pname, e.name 
FROM emp e RIGHT JOIN project p ON e.empno = p.empno;

Output Table Example:

pid	pname	name
P1	Website	Alam
P2	Database	Ravi
P3	App	NULL

Key Differences Summary

Join Type	Description	Duplicate Columns	NULL Handling
CROSS JOIN	All combinations of rows	No	N/A
EQUI-JOIN	Matches based on equality, shows all columns	Yes	Only matching rows
NATURAL JOIN	Matches based on equality, hides duplicate columns	No	Only matching rows
LEFT JOIN	All rows from left table + matching from right	Depends	NULL for non-matching right rows
RIGHT JOIN	All rows from right table + matching from left	Depends	NULL for non-matching left rows