DAY 16

🔗 SQL Joins & Meta Information

Ghaziabad Mohan Nagar

Design Drafted by: HR Prateek Sharma

📊 Meta Information

Information of Information

🔗 9 Types of SQL Joins

1

Inner Join

Common data from both tables

Returns only the matching records from both left and right tables. If there's no match, the record is excluded.

Left

⊙

Right

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.ID = TableB.ID

2

Left Join

All left + common from right

Returns all records from the left table and matching records from the right table. Non-matching right records show NULL.

ALL

←

Match

SELECT * FROM TableA
LEFT JOIN TableB
ON TableA.ID = TableB.ID

3

Right Join

All right + common from left

Returns all records from the right table and matching records from the left table. Non-matching left records show NULL.

Match

→

ALL

SELECT * FROM TableA
RIGHT JOIN TableB
ON TableA.ID = TableB.ID

4

Full Join

All left + all right + common

Returns all records when there's a match in either left or right table. Non-matching records show NULL for missing side.

ALL

∪

ALL

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.ID = TableB.ID

5

Left Anti Join

Only left, not in right

Returns records from the left table that have no matching records in the right table. Excludes all common records.

Only

⊖

Right

SELECT * FROM TableA
LEFT JOIN TableB
ON TableA.ID = TableB.ID
WHERE TableB.ID IS NULL

6

Right Anti Join

Only right, not in left

Returns records from the right table that have no matching records in the left table. Excludes all common records.

Left

⊖

Only

SELECT * FROM TableA
RIGHT JOIN TableB
ON TableA.ID = TableB.ID
WHERE TableA.ID IS NULL

7

Inner Except Join

All records except inner/common

Returns all records from both tables except the matching ones. Shows only non-matching records from both sides.

Only

⊕

Only

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.ID = TableB.ID
WHERE TableA.ID IS NULL
OR TableB.ID IS NULL

8

Disjoint Join

No common records

Returns records from both tables where there are no matching keys. Identifies completely separate datasets with no overlap.

No

⊗

Match

SELECT * FROM TableA, TableB
WHERE NOT EXISTS
(SELECT 1 WHERE
TableA.ID = TableB.ID)

9

Self Join

Table joins with itself

A table is joined with itself to compare rows within the same table. Useful for hierarchical data or finding duplicates.

Same

↻

Table

SELECT A.*, B.*
FROM TableA AS A
INNER JOIN TableA AS B
ON A.ManagerID = B.EmployeeID

≡ Join Equivalences

🔄 Important Join Equivalences

Left Join

===

Left Outer Join

Both return the same results

Right Join

===

Right Outer Join

Both return the same results

NULL

NULL = Value Not Found

In SQL and database operations, NULL represents the absence of a value. It's not zero, not an empty string, but literally "no data". When joins don't find matching records, the corresponding columns are filled with NULL values, indicating that no matching data exists in that table.

📊 Quick Reference Table

#	Join Type	Returns	Use Case
1	Inner Join	Only matching records	Find exact matches between tables
2	Left Join	All left + matching right	Keep all left records, match if possible
3	Right Join	All right + matching left	Keep all right records, match if possible
4	Full Join	All records from both	Combine everything, show all data
5	Left Anti Join	Left only (no match in right)	Find records unique to left table
6	Right Anti Join	Right only (no match in left)	Find records unique to right table
7	Inner Except Join	All except matching	Find all non-matching records
8	Disjoint Join	No common records	Identify completely separate datasets
9	Self Join	Table with itself	Compare rows within same table

💡 Key Learning Points

📊 Meta Information

🔗 9 Join Types

⊙ Inner Join

← Left Join

→ Right Join

∪ Full Join

⊖ Anti Joins

⊕ Except Join

↻ Self Join

≡ Join Equivalences

∅ NULL Values

SQL Joins & Meta Information - Day 16

Master Data Relationships & Join Operations

Ghaziabad Mohan Nagar

Designed by HR Prateek Sharma

Last Updated: December 6, 2025