MySQL
JOINS
Cheat Sheet
INNER JOIN
Returns only the rows where there is a match in both tables based on the specified condition.
Removes unmatched rows.
Most commonly used JOIN.
Syntax:
SELECT X.column1, Y.column2 FROM TableA X INNER JOIN TableB Y ON X.id = Y.id;
Orders Table:
Order_ID Customer_ID Product Quantity
101 C001 Laptop 1
102 C002 Tablet 2
103 C003 Monitor 1
104 C004 Keyboard 3
105 C005 Headphones 1
Customer Table:
Customer_ID Customer_Name City Tier
C001 John Smith New York Gold
C002 Alice Green Chicago Silver
C003 Raj Kapoor Mumbai Gold
C004 Sara Khan Dubai Bronze
C006 David Miller Paris Silver
Query: SELECT O.OrderID, O.Product, C.CustomerName, C.City FROM Orders O INNER JOIN
Customers C ON O.CustomerID = C.CustomerID;
Expected Output:
Order_ID Product Customer_Name City
101 Laptop John Smith New York
102 Tablet Alice Green Chicago
103 Monitor Raj Kapoor Mumbai
104 Keyboard Sara Khan Dubai
LEFT JOIN
Returns all rows from the left table, and matched rows from the right table. If no match exists, NULLs
are returned from the right side.
Keeps all data from the left table
Fills unmatched right-side columns with NULL
Syntax:
SELECT X.column1, Y.column2 FROM TableA X LEFT JOIN TableB Y ON X.id = Y.id;
Orders Table:
Order_ID Customer_ID Product Quantity
101 C001 Laptop 1
102 C002 Tablet 2
103 C003 Monitor 1
104 C004 Keyboard 3
105 C005 Headphones 1
Customer Table:
Customer_ID Customer_Name City Tier
C001 John Smith New York Gold
C002 Alice Green Chicago Silver
C003 Raj Kapoor Mumbai Gold
C004 Sara Khan Dubai Bronze
C006 David Miller Paris Silver
Query: SELECT C.CustomerName, C.City, O.Product, O.Quantity FROM Customers C LEFT JOIN
Orders O ON C.CustomerID = O.CustomerID;
Expected Output:
Customer_Name City Product Quantity
John Smith New York Laptop 1
Alice Green Chicago Tablet 2
Raj Kapoor Mumbai Monitor 1
Sara Khan Dubai Keyboard 3
David Miller Paris Null Null
RIGHT JOIN
Returns all rows from the right table, and matched rows from the left table. If no match exists,
NULLs are returned from the left side.
Keeps all data from the right table.
Fills unmatched left-side columns with NULL
Syntax:
SELECT X.column1, Y.column2 FROM TableA X RIGHT JOIN TableB Y ON X.id = Y.id;
Orders Table:
Order_ID Customer_ID Product Quantity
101 C001 Laptop 1
102 C002 Tablet 2
103 C003 Monitor 1
104 C004 Keyboard 3
105 C005 Headphones 1
Customer Table:
Customer_ID Customer_Name City Tier
C001 John Smith New York Gold
C002 Alice Green Chicago Silver
C003 Raj Kapoor Mumbai Gold
C004 Sara Khan Dubai Bronze
C006 David Miller Paris Silver
Query: SELECT O.OrderID, O.Product, C.CustomerName, C.City FROM Customers C RIGHT JOIN
Orders O ON C.CustomerID = O.CustomerID;
Expected Output:
Order_ID Product Customer_Name City
101 Laptop John Smith New York
102 Tablet Alice Green Chicago
103 Monitor Raj Kapoor Mumbai
104 Keyboard Sara Khan Dubai
105 Headphones Null Null
FULL OUTER JOIN
Returns all rows from both tables, with matched rows where possible. If there's no match, the
missing side is filled with NULL.
Combines LEFT + RIGHT JOIN behavior
Shows everything: matched + unmatched from both sides
Syntax:
SELECT X.column1, Y.column2 FROM TableA X FULL OUTER JOIN TableB Y ON X.id = Y.id;
Orders Table:
Order_ID Customer_ID Product Quantity
101 C001 Laptop 1
102 C002 Tablet 2
103 C003 Monitor 1
105 C005 Headphones 1
Customer Table:
Customer_ID Customer_Name City Tier
C001 John Smith New York Gold
C002 Alice Green Chicago Silver
C003 Raj Kapoor Mumbai Gold
C006 David Miller Paris Silver
Query: SELECT C.CustomerName, C.City, O.Product, O.QuantityFROM Customers C FULL OUTER
JOIN Orders O ON C.CustomerID = O.CustomerID;
Expected Output:
Customer_Name City Product Quantity
John Smith New York Laptop 1
Alice Green Chicago Tablet 2
Raj Kapoor Mumbai Monitor 1
David Miller Dubai Null Null
Null Null Headphones 1
CROSS JOIN
Returns the Cartesian product of the two tables – every row of the first table is paired with
every row of the second table.
No ON clause is used.
Can produce large result sets.
Often used for generating combination.
Syntax:
SELECT X.column1, Y.column2 FROM TableA X CROSS JOIN TableB Y;
Employees Table:
EMP_ID Employee_Name
E01 Alice
E02 Sam
E03 Riya
Project Table:
Project_ID Project_Name
P1 Alpha
P2 Beta
Query: SELECT E.EmployeeName, P.ProjectName FROM Employees E CROSS JOIN Projects P;
Expected Output:
Employee_Name Project_Name
Alice Alpha
Alice Beta
Sam Alpha
Sam Beta
Riya Alpha
Riya Beta
