Types of JOINS in SQL

1. Use INNER JOIN when:
   • You only want matching records between two tables.
   • You want to remove NULL values from the second table.

2. Use LEFT JOIN (or LEFT OUTER JOIN) when:
   • You want all records from the first (left) table, even if there’s no match in the second table.
   • You still want NULL values from the second table for unmatched records.

3. Use RIGHT JOIN (or RIGHT OUTER JOIN) when:
   • You want all records from the second (right) table, even if there’s no match in the first table.
   • (SIMILAR TO LEFT JOIN)

3. Use FULL OUTER JOIN when:
   • You want all records from both tables, even if they don’t match.
   • (AVOID BCZ SLOW)

3. Use CROSS JOIN when: (RARELY USED)
   • You want every combination of records from both tables (Cartesian Product).
   • You don’t need a join condition.
   • You don’t need a join condition.
   • Ex- SELECT a.machine, b.handover FROM item_master a CROSS JOIN txm_motor_handover b;

Best Join	Scenario
INNER JOIN	Only need matching data
LEFT JOIN	Need all records from the first table, even if no match
RIGHT JOIN	Need all records from the second table, even if no match
FULL OUTER JOIN	Need all records from both tables
CROSS JOIN	Need every combination of rows

Practical Advice

1. Use INNER JOIN most of the time because it is the most efficient.
2. Use LEFT JOIN when you need unmatched records from the left table.
3. Avoid FULL OUTER JOIN unless necessary because it is slow on large datasets.
4. Only use CROSS JOIN if you really need all combinations.

Learn More