Comparing with LEFT JOIN (LEFT OUTER JOIN, RIGHT JOIN, INNER JOIN, COMMA JOIN, CROSS JOIN)

LEFT JOIN

A method that joins the right table based on the left table; if there is no matching data, it returns NULL.

SELECT u.SEQ
     , u.NAME
     , c.CODE_NAME
 FROM USER u 
 LEFT JOIN CODE c
   ON u.CODE_SEQ = c.CODE_SEQ;

RIGHT JOIN

Features

A method that joins the left table based on the right table; if there is no matching data, it is treated as NULL

Example

-- Outputs the same result as the LEFT JOIN above
SELECT u.SEQ
     , u.NAME
     , c.CODE_NAME
  FROM CODE c 
 RIGHT JOIN USER u
    ON c.CODE_SEQ = u.CODE_SEQ;

RIGHT JOIN (vs. LEFT JOIN)

Since reading habits typically follow left-to-right, LEFT JOIN is overwhelmingly more commonly used than RIGHT JOIN.

The results are perfectly identical.

LEFT OUTER JOIN

Features

• OUTER exists for syntactic clarity
• There is no difference between LEFT JOIN and LEFT OUTER JOIN.

Example

-- Outputs the same result as the LEFT JOIN above
SELECT u.SEQ
     , u.NAME
     , c.CODE_NAME
 FROM USER u 
 LEFT OUTER JOIN CODE c
   ON u.CODE_SEQ = c.CODE_SEQ;

LEFT OUTER JOIN (vs. LEFT JOIN)

LEFT OUTER JOIN explicitly emphasizes the inclusion of unmatched data.

LEFT JOIN = LEFT OUTER JOIN

LEFT INNER JOIN (= JOIN)

Features

Includes only the intersection of the two tables that satisfy the JOIN condition in the result

Example (LEFT INNER JOIN)

SELECT u.SEQ
     , u.NAME
     , c.CODE_NAME
  FROM USER u
 INNER JOIN CODE c
    ON u.CODE_SEQ = c.CODE_SEQ;

JOIN

JOIN also displays the intersection and produces the same result as INNER JOIN

Example (JOIN)

-- Outputs the same result as the INNER JOIN above
SELECT u.SEQ
     , u.NAME
     , c.CODE_NAME
  FROM USER u 
  JOIN CODE c
    ON u.CODE_SEQ = c.CODE_SEQ;

LEFT INNER JOIN (vs. LEFT JOIN)

Data Processing Efficiency

• JOIN (INNER) : Finds rows present in both tables, enabling pruning and a smaller result set
• LEFT JOIN : Retains all rows from the left table, requiring additional NULL filling operations

Indexes and Optimization

• JOIN (INNER) : DBMS optimizer selects more efficient join order (Nested Loop, Hash Join, etc.), filtering unnecessary rows early
• LEFT JOIN : Requires guaranteeing full scan, high risk of full table scan

Result Scope

• JOIN (INNER) : Only matching rows (intersection)
• LEFT JOIN : Entire left table + matching/NULL rows

Matching Failure

• JOIN (INNER) : Row exclusion
• LEFT JOIN : Right side filled with NULL

Performance

• JOIN (INNER) : Relatively fast
• LEFT JOIN : Can be slow

However, if indexes are well-configured, the performance difference may be negligible.

COMMA JOIN

Tables are listed with commas (,) in the FROM clause; the JOIN condition is handled in the WHERE clause.

Features

Does not use the JOIN keyword directly

Example

-- Outputs the same result as the INNER JOIN above
SELECT u.SEQ
     , u.NAME
     , c.CODE_NAME
  FROM USER u
     , CODE c
 WHERE u.CODE_SEQ = c.CODE_SEQ;

COMMA JOIN (vs. INNER JOIN)

INNER JOIN defines conditions in the WHERE clause, making it more prone to errors compared to COMMA JOIN.

Syntax

• INNER JOIN : Explicit ON clause
• COMMA JOIN : WHERE clause condition

Priority

• INNER JOIN : High (Safe with LEFT JOIN, etc.)
• COMMA JOIN : Low (May cause errors)

Readability

• INNER JOIN : High (Clear JOIN conditions)
• COMMA JOIN : Low (Confusing in complex queries)

INNER JOIN is more recommended standard than COMMA JOIN these days.

CROSS JOIN

Combines all rows from two tables to generate a cartesian product

Features

• Returns all possible row pairs without any join conditions
• No ON clause (no JOIN conditions)
• Used when generating all possible combinations

Example

SELECT u.SEQ
     , u.NAME
     , c.CODE_NAME
  FROM USER u
 CROSS JOIN CODE c

CROSS JOIN (vs. LEFT JOIN)

Keyword

• CROSS JOIN : All combinations (Cartesian Product)
• LEFT JOIN : Preserves original table

JOIN Condition (ON)

• CROSS JOIN : None
• LEFT JOIN : Required

Result rows

• CROSS JOIN : (Number of rows in Table A) × (Number of rows in Table B)
• LEFT JOIN : At least the number of rows in Table A

NULL Occurrence

• CROSS JOIN : Does not occur
• LEFT JOIN : Occurs if right table has no matching rows

Performance

• CROSS JOIN : Exponential increase with large volumes
• LEFT JOIN : Can be optimized with conditions