SQL Server Tutorialprovides basic and advanced concepts of C# for beginners and professionals.

  1. Home
  2. SQL Server Tutorial

SQL Joins

Back to: SQL Server Tutorial

Joins in SQL are used to combine rows from two or more tables based on a related column between them. In Microsoft SQL Server (MS SQL), there are several types of joins, each serving different purposes. Here’s a quick overview:

1. INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

Example:

SELECT employees.EmployeeID, employees.Name, departments.DepartmentName FROM employees INNER JOIN departments ON employees.DepartmentID = departments.DepartmentID;

2. LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all rows from the left table and the matched rows from the right table. The result is NULL from the right side if there is no match.

Example:

SELECT employees.EmployeeID, employees.Name, departments.DepartmentName FROM employees LEFT JOIN departments ON employees.DepartmentID = departments.DepartmentID;

3. RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all rows from the right table and the matched rows from the left table. The result is NULL from the left side when there is no match.

Example:

SELECT employees.EmployeeID, employees.Name, departments.DepartmentName FROM employees RIGHT JOIN departments ON employees.DepartmentID = departments.DepartmentID;

4. FULL JOIN (or FULL OUTER JOIN)

The FULL JOIN keyword returns all rows when there is a match in either left or right table. If there is no match, the result is NULL on the side that does not have a match.

Example:

SELECT employees.EmployeeID, employees.Name, departments.DepartmentName FROM employees FULL JOIN departments ON employees.DepartmentID = departments.DepartmentID;

5. CROSS JOIN

The CROSS JOIN keyword returns the Cartesian product of the two tables. It means every row of the first table is combined with every row of the second table.

Example:

SELECT employees.EmployeeID, departments.DepartmentName FROM employees CROSS JOIN departments;

6. SELF JOIN

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

Example:

SELECT e1.EmployeeID AS EmployeeID1, e1.Name AS EmployeeName1, e2.EmployeeID AS EmployeeID2, e2.Name AS EmployeeName2 FROM employees e1 INNER JOIN employees e2 ON e1.ManagerID = e2.EmployeeID;

Key Points:

  • Join Conditions: Joins typically involve comparing columns between the tables, often using primary and foreign keys.
  • Performance: Be mindful of performance impacts, especially with large datasets. Proper indexing can help optimize joins.
  • Aliasing: Use table aliases (like e1 and e2 in the SELF JOIN example) to simplify queries and improve readability.
Scroll to Top