Given two tables t1 and t2. List all types of joins you know, and the result of the query select * from t1 <join> t2 on t1.t = t2.t for each. |t1.t| ------ 1 2 4 null ------ |t2.t| ------ 1 3 null null

Let's consider the types of JOINs and the result for the query select * from t1 <join> t2 on t1.t = t2.t on the data:

t1.t
1
2
4
null

t2.t
1
3
null
null

---

1. INNER JOIN Returns rows where the join condition is true (t1.t = t2.t).

Result:

t1.t	t2.t
1	1

null is not equal to null in SQL, so rows with null do not join.

---

2. LEFT JOIN (LEFT OUTER JOIN) Returns all rows from t1 and matching rows from t2; if no match, NULLs in t2 columns.

Result:

t1.t	t2.t
1	1
2	NULL
4	NULL
NULL	NULL

Row with NULL from t1 does not join with NULL from t2, but the row from t1 is included in the result.

---

3. RIGHT JOIN (RIGHT OUTER JOIN) All rows from t2 and matching rows from t1; otherwise NULL in t1.

Result:

t1.t	t2.t
1	1
NULL	3
NULL	NULL
NULL	NULL

---

4. FULL JOIN (FULL OUTER JOIN) All rows from t1 and t2; matching rows are joined, others are filled with NULL.

Result:

t1.t	t2.t
1	1
2	NULL
4	NULL
NULL	NULL
NULL	3
NULL	NULL

---

5. CROSS JOIN Cartesian product of all rows from t1 and t2, without condition.

Result (total 4 rows t1 * 4 rows t2 = 16):

t1.t	t2.t
1	1
1	3
1	NULL
1	NULL
2	1
2	3
2	NULL
2	NULL
4	1
4	3
4	NULL
4	NULL
NULL	1
NULL	3
NULL	NULL
NULL	NULL

---

Thus, the main types of JOINs and their behavior on the data are described above.