What is the difference between the join types?
Understand the concept
A join combines rows from two table expressions according to a relationship or condition. Inner joins keep matching pairs, while outer joins also keep unmatched rows from specified sides with NULL values for the missing side. The correct join type follows the business requirement about which records must remain visible.
Important ideas
-
INNER: matching rows. LEFT/RIGHT: keep all rows from that side.
-
FULL: keep unmatched rows from both sides; CROSS: every possible pair.
-
SELF JOIN: join a table to itself, such as employees to managers.
-
A self join uses aliases to treat the same table as two roles, such as employee and manager.
Can you explain it without the notes?
What is the difference between the join types?
Use your own words. Explain an example and one limitation before checking the explanation.
Check your explanation
A join combines rows from two table expressions according to a relationship or condition. Inner joins keep matching pairs, while outer joins also keep unmatched rows from specified sides with NULL values for the missing side. The correct join type follows the business requirement about which records must remain visible.
Turn this concept into a clear answer.
- Before writing SQL, say whether rows with no match should appear in the answer.
Adapted from 05_SQL_POSTGRESQL.md · “What is the difference between the join types?”. Examples are study fragments; framework behavior can depend on your version.