In a normalized database, the data to be viewed can be stored in multiple tables. When you need to view data from related tables together, you can query the data by joining the tables with the help of common attributes. You can also use subqueries where the result of a query is used as an input for the condition of another query.
This article discusses about how to query data from multiple tables by applying various types of joins, such as an inner join, outer join, cross join, equijoin, or self-join.
Depending on the requirements to view data from multiple tables, you can apply different types of joins listed below:
A join is implemented by using the SELECT statement, where the SELECT list contains the name of the columns to be retrieved from the tables. The FROM clause contains the names of the tables from which combined data is to be retrieved. The WHERE clause specifies the condition, with a comparison operator, based on which the tables will be joined.
The syntax of applying an inner join in the SELECT query is:
SELECT column_name, column_name [, column_name]
FROM table1_name JOIN table2_name
ON table1_name.ref_column_name join_operator
Table2_name.ref_column_name
Where
The following query displays the Employee ID and Title for each employee from the Emoloyee table and the Rate and PayFrequency columns from the EmployeePayHistory table
SELECT e.BusinessEntityID,e.JobTitle, eph.Rate, eph.PayFrequency
from HumanResources.Employee e
JOIN HumanResources.EmployeePayHistory eph
ON e.BusinessEntityID = eph.BusinessEntityID
In the preceding query, the Employee and EmployeePayHistory tables are joined on the common column, ID. The query also assigns e as the alias of the Employee table and eph as the alias of the EmployeePayHistory table. The column names are also listed with the table alias names.
Based on the relationship between tables, you need to select the common column to set the join condition. In the preceding example, the common column is ID, which is the primary key of the Employee table and the foreign key of the EmployeePayHistory table.
Observe the result set after the join. The record of the employee with D as 1 from the Employee table is joined with the record of the employee with ID as 1 from the EmployeePayHistory table.
This article discusses about how to query data from multiple tables by applying various types of joins, such as an inner join, outer join, cross join, equijoin, or self-join.
Querying Data by Using Joins
As a database developer, you may need to retrieve data from more than one table together as a part of a single result set. In such a case, different columns in the result set can obtain data from different tables. To retrieve data from multiple tables, the SQL Server allows you to apply joins. Joins allow you to view data from related tables in a single result set. You can join more than one table based on common attribute.Depending on the requirements to view data from multiple tables, you can apply different types of joins listed below:
Inner Join
An inner join retrieves records from multiple tables by using a comparison operator on a common column. When an inner join is applied, 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.A join is implemented by using the SELECT statement, where the SELECT list contains the name of the columns to be retrieved from the tables. The FROM clause contains the names of the tables from which combined data is to be retrieved. The WHERE clause specifies the condition, with a comparison operator, based on which the tables will be joined.
The syntax of applying an inner join in the SELECT query is:
SELECT column_name, column_name [, column_name]
FROM table1_name JOIN table2_name
ON table1_name.ref_column_name join_operator
Table2_name.ref_column_name
Where
- Table1_name and table2_name are the name of the tables that are joined join_operator is the comparison operator based on which the join is applied
- Table1_name.ref_column_name and table2_name.ref_column_name are the names of the columns on which the join is applied.
The following query displays the Employee ID and Title for each employee from the Emoloyee table and the Rate and PayFrequency columns from the EmployeePayHistory table
SELECT e.BusinessEntityID,e.JobTitle, eph.Rate, eph.PayFrequency
from HumanResources.Employee e
JOIN HumanResources.EmployeePayHistory eph
ON e.BusinessEntityID = eph.BusinessEntityID
In the preceding query, the Employee and EmployeePayHistory tables are joined on the common column, ID. The query also assigns e as the alias of the Employee table and eph as the alias of the EmployeePayHistory table. The column names are also listed with the table alias names.
Based on the relationship between tables, you need to select the common column to set the join condition. In the preceding example, the common column is ID, which is the primary key of the Employee table and the foreign key of the EmployeePayHistory table.
Observe the result set after the join. The record of the employee with D as 1 from the Employee table is joined with the record of the employee with ID as 1 from the EmployeePayHistory table.