Table Joins (INNER, LEFT, RIGHT, FULL OUTER, CROSS) INNER JOIN An INNER JOIN is a type of join that only returns rows where the two tables have matching...
Table Joins (INNER, LEFT, RIGHT, FULL OUTER, CROSS)
INNER JOIN
An INNER JOIN is a type of join that only returns rows where the two tables have matching values in both columns. For example, consider the following two tables:
sql
Table A (
id INT,
name VARCHAR(50)
)
Table B (
id INT,
name VARCHAR(50),
address VARCHAR(100)
)
If we perform an INNER JOIN on these tables based on the "id" column, we will only return the rows where the "id" column is the same in both tables. In this case, the result will be:
sql
id | name | address
--- | --- | --------
1 | John | NULL
2 | Mary | NULL
The "address" column is not included in the result because there is no matching value in the "address" column of the "Table B" table.
LEFT JOIN
A LEFT JOIN is a type of join that returns all rows from the left table, even if there is no matching value in the right table. The right table is considered a "source" and its rows are joined to the left table based on the matching columns. For example:
sql
SELECT A.id, A.name, B.address
FROM TableA AS A
LEFT JOIN TableB AS B ON A.id = B.id
The result of this query will be:
sql
id | name | address
--- | --- | --------
1 | John | NULL
2 | Mary | NULL
3 | Bob | NULL
As you can see, all rows from the "TableA" table are included in the result, even though there is no matching row in the "TableB" table for the "id" = 3 row.
RIGHT JOIN
A RIGHT JOIN is similar to a LEFT JOIN, but it returns all rows from the right table, even if there is no matching value in the left table. The left table is considered a "source" and its rows are joined to the right table based on the matching columns.
FULL OUTER JOIN
A FULL OUTER JOIN returns all rows from both tables, even if there is no matching value in either table. The left table is considered the "source" and its rows are joined to the right table based on the matching columns. The right table is considered the "destination" and its rows are joined to the left table based on the matching columns.
For example:
sql
SELECT A.id, A.name, B.address
FROM TableA AS A
FULL OUTER JOIN TableB AS B ON A.id = B.id
The result of this query will be:
sql
id | name | address
--- | --- | --------
1 | John | NULL
2 | Mary | NULL
3 | Bob | NULL
4 | NULL | NULL
As you can see, all rows from the "TableA" and "TableB" tables are included in the result, even though there is no matching row in the "TableB" table for the "id" = 4 row.
CROSS JOIN
A CROSS JOIN is a type of join that combines all rows from both tables. The result is a Cartesian product of the two tables, where each row from the first table is combined with each row from the second table. For example:
sql
SELECT A.id, A.name, B.address
FROM TableA AS A
CROSS JOIN TableB AS B ON A.id = B.id
The result of this query will be:
sql
id | name | address
--- | --- | --------
1 | John | NULL
1 | Mary | NULL
2 | Bob | NULL
3 | John | NULL
3 | Mary | NULL
As you can see, all rows from the "TableA" and "TableB" tables are included in the result