MSDN Tutorials

INNER JOIN

Returns only the rows where the join condition is met in both tables. If a row in one table doesn't have a match in the other, it's excluded.

Syntax:

SELECT column_list
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example: Retrieving customer names and their corresponding order dates.

LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table, and the matched rows from the right table. If there is no match in the right table, NULL values are returned for its columns.

Syntax:

SELECT column_list
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example: Listing all customers and any orders they may have placed (customers without orders will still be listed with NULL order details).

RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table, and the matched rows from the left table. If there is no match in the left table, NULL values are returned for its columns.

Syntax:

SELECT column_list
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example: Listing all orders and the customer who placed them (orders placed by unknown customers or without assigned customers will still be listed).

FULL JOIN (or FULL OUTER JOIN)

Returns all rows when there is a match in either the left or the right table. If there is no match, the missing side will have NULL values.

Syntax:

SELECT column_list
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Example: Showing all customers and all orders, linking them where possible and showing unmatched records from either side.

CROSS JOIN

Returns the Cartesian product of the two tables. It pairs every row from the first table with every row from the second table. This is often used for generating combinations or testing.

Syntax:

SELECT column_list
FROM table1
CROSS JOIN table2;

Note: Be cautious with CROSS JOIN on large tables as it can produce a very large result set.

SELF JOIN

A self join is a regular join, but the table is joined with itself. This is useful for querying hierarchical data or comparing rows within the same table.

Example:

Finding employees and their managers from an 'Employees' table where both employee and manager information are in the same table.

SELECT e.EmployeeName, m.EmployeeName AS ManagerName
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;