SQL Server provides a method of retrieving data from more than one table using joins. A join can be defined as an operation that includes the retrieval of data from more than one table at a time.
A join is implemented using the SELECT statement, in which the SELECT list contains the name of the columns to be retrieved from the tables. The FROM clause contains the name of tables from which combined data is to be retrieved. The WHERE specifies the rows to be included in the result set with the help of the join operator.
- Inner Join
- Outer Join
- Cross Join
- Equi Join
- Natural Join
- Self Join
Inner Joins:
In inner join, data from multiple tables is displayed after comparing values present in a common column. Only rows with values satisfying the join condition in the common column are displayed. Rows in both tables that do not satisfy the join condition are not displayed.
Syntax:
SELECT Column_name, column_name, [column_name...n]
From table_name JOIN table_name
ON table_name.ref_column_name join_operator table_name.ref_column_name
Outer Joins:
A join can be termed as outer join when the result set contains all rows from one table and the matching rows from another. An outer join displays NULL for the columns of the related table where it does not find matching records.
Outer join is of two types
- Left outer join
- Right outer join
Left outer joins ensures the inclusion of all rows from the first table and the matching rows from the second table.
Right outer join ensures the inclusions of all rows from the second table and matching rows from the first table.
Syntax:
SELECT Column_name, column_name, [column_name...n]
From table_name [LEFT| RIGHT] OUTER JOIN table_name
ON table_name.ref_column_name join_operator table_name.ref_column_name
Cross Join:
A join that includes more than one table using the keyword CROSS is called a cross join. The output of such joins is called a Cartesian product. In a cross join between two tables, each row from the first table in joined with each row from the second table. The number of rows in the result set is the number of rows in the first table multiplied by the number of rows in the second table.
Example:
If a join is performed on a table named Titles that has 18 rows and another named Publishers with 8 rows the result will be 144 rows, the Cartesian product of two tables.
SELECT * FROM Titles CROSS JOIN publishers
Where the FROM clause defines the tables from which data is to be retrieved.
Equi Join:
A join that uses an asterisk (*) sign in the SELECT list and displays redundant column data in the result set is termed as an equi join. An equi join displays redundant column data in the result set, where two or more tables are compared for equality.
Example:
SELECT * FROM Sales s JOIN Titles t ON s.Title_Id =t.Title_Id
JOIN Publishers p ON t.Pub_Id = p.Pub_Id
The output produced by the above query results in redundant column data from the three tables.
Natutal Join:
A join that restricts the redundant column data from the result set is known as a natural join. It is implemented by specifying the various column names in the SELECT list. Consider the following example in which the title name and the publisher of the book are selected from the Titles and Publishers tables and displayed.
Example:
SELECT t.Title, p.Pub_Name FROM Titles t JOIN Publishers ON t.Pub_Id = p.Pub_Id
In the above example, t is an alias for the Titles table and p is an alias for the Publishers table. These aliases are used to distinguish the table to which the column Pub_Id belongs
Self Join:
A join is said to be a self join when one row in a table correlates with other rows in the same table. Since the same table is used twice for comparison, an alias name differentiates the two copies of the table. All join operators except the outer join operators can be used in a self join.
Example:
