05 Intermediary
Join¶
1st table’s order is always followed.
| Outer | Inner | Natural | |
|---|---|---|---|
| working | uses null for missing values | selects all rows from both tables as long as there is a match between the columns. | only common tuples with only the left table |
| Returns records that have matching values in both tables | |||
| condition | a column name in both the tables must be same | ||
| common table repeated | ✅ | ✅ | ❌ |
select *
from t1 left outer join t2 on t1.roll = t2.rollNum;
select *
from t1 inner join t2 on t1.roll = t2.rollNum;
select *
from t1 natural join t2;
Outer Join¶
| Left Outer Join | Right Outer Join | Full outer Join |
|---|---|---|
| Left table’s tuples will occur once | Right table’s tuples will occur once | |
| Returns all records from the left table, and the matched records from the right table | Returns all records from the right table, and the matched records from the left table | Returns all records when there is a match in either left or right table |
2023-01-25