DBMS SQL JOINS

SQL JOIN

A clause used to combine rows from two or more tables based on a related column between them.

INNER JOIN

Returns only the rows where there is a match in both joined tables.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matched rows from the right table. If no match, NULLs are shown for the right side.

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table and matched rows from the left table. If no match, NULLs are shown for the left side.

FULL OUTER JOIN

Returns all rows from both tables, with NULLs for unmatched rows on either side.

Join Condition

A condition (usually using ON) that defines how two tables should be matched based on related columns.

NULL in Joins

When a row from one table has no matching row in the other table, the unmatched columns return NULL.

Join Syntax (INNER JOIN)

SELECT *

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

Cartesian Product

Occurs when tables are joined without a proper ON clause, resulting in every row of one table matched with every row of the other.

Practical Use of JOIN

Used to fetch related data from multiple tables, such as customers and their orders, or students and their courses.