{"id":1070,"date":"2019-02-10T18:16:31","date_gmt":"2019-02-10T11:16:31","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1070"},"modified":"2024-03-02T16:02:53","modified_gmt":"2024-03-02T09:02:53","slug":"sql-server-joins","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-joins\/","title":{"rendered":"SQL Server Joins"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about various SQL Server joins that allow you to combine data from two tables.<\/p>\n\n\n\n

In a relational database, data is distributed in multiple logical tables. To get a complete meaningful set of data, you need to query data from these tables using joins. SQL Server supports many kinds of joins, including inner join<\/a>, left join<\/a>, right join<\/a>, full outer join<\/a>, and cross join<\/a>. Each join type specifies how SQL Server uses data from one table to select rows in another table.<\/p>\n\n\n\n

Let’s set up sample tables for demonstration.<\/p>\n\n\n\n

Setting up sample tables #<\/a><\/h2>\n\n\n\n

First, create a new schema named hr<\/code>:<\/p>\n\n\n

CREATE<\/span> SCHEMA<\/span> hr;\nGO<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> TABLE<\/span> hr.candidates(\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> IDENTITY<\/span>,\n    fullname VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>\n);\n\nCREATE<\/span> TABLE<\/span> hr.employees(\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> IDENTITY<\/span>,\n    fullname VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> \n    hr.candidates(fullname)\nVALUES<\/span>\n    ('John Doe'<\/span>),\n    ('Lily Bush'<\/span>),\n    ('Peter Drucker'<\/span>),\n    ('Jane Doe'<\/span>);\n\n\nINSERT<\/span> INTO<\/span> \n    hr.employees(fullname)\nVALUES<\/span>\n    ('John Doe'<\/span>),\n    ('Jane Doe'<\/span>),\n    ('Michael Scott'<\/span>),\n    ('Jack Sparrow'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s call the candidates<\/code> table the left table and the employees<\/code> table the right table.<\/p>\n\n\n\n
SQL Server Inner Join #<\/a><\/h2>\n\n\n\n
Inner join<\/a> produces a data set that includes rows from the left table, and matching rows from the right table.<\/p>\n\n\n\n
The following example uses the inner join clause to get the rows from the candidates<\/code> table that has the corresponding rows with the same values in the fullname<\/code> column of the employees<\/code> table:<\/p>\n\n\n
SELECT<\/span>  \n    c.id candidate_id,\n    c.fullname candidate_name,\n    e.id employee_id,\n    e.fullname employee_name\nFROM<\/span> \n    hr.candidates c\n    INNER<\/span> JOIN<\/span> hr.employees e \n        ON<\/span> e.fullname = c.fullname;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The following Venn diagram illustrates the result of the inner join of two result sets:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
SQL Server Left Join #<\/a><\/h2>\n\n\n\n
Left join<\/a> selects data starting from the left table and matching rows in the right table. The left join returns all rows from the left table and the matching rows from the right table. If a row in the left table does not have a matching row in the right table, the columns of the right table will have nulls.<\/p>\n\n\n\n
The left join is also known as the left outer join. The outer keyword is optional.<\/p>\n\n\n\n
The following statement joins the candidates<\/code> table with the employees<\/code> table using left join:<\/p>\n\n\n
SELECT<\/span>  \n\tc.id candidate_id,\n\tc.fullname candidate_name,\n\te.id employee_id,\n\te.fullname employee_name\nFROM<\/span> \n\thr.candidates c\n\tLEFT<\/span> JOIN<\/span> hr.employees e \n\t\tON<\/span> e.fullname = c.fullname;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The following Venn diagram illustrates the result of the left join of two result sets:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To get the rows that are available only in the left table but not in the right table, you add a WHERE<\/code> clause to the above query:<\/p>\n\n\n
SELECT<\/span>  \n    c.id candidate_id,\n    c.fullname candidate_name,\n    e.id employee_id,\n    e.fullname employee_name\nFROM<\/span> \n    hr.candidates c\n    LEFT<\/span> JOIN<\/span> hr.employees e \n        ON<\/span> e.fullname = c.fullname\nWHERE<\/span> \n    e.id IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The following Venn diagram illustrates the result of the left join that selects rows available only in the left table:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
SQL Server Right Join #<\/a><\/h2>\n\n\n\n
The right join<\/a> or right outer join<\/a> selects data starting from the right table. It is a reversed version of the left join<\/a>.<\/p>\n\n\n\n
The right join returns a result set that contains all rows from the right table and the matching rows in the left table. If a row in the right table does not have a matching row in the left table, all columns in the left table will contain nulls.<\/p>\n\n\n\n
The following example uses the right join to query rows from candidates<\/code> and employees<\/code> tables:<\/p>\n\n\n
SELECT<\/span>  \n    c.id candidate_id,\n    c.fullname candidate_name,\n    e.id employee_id,\n    e.fullname employee_name\nFROM<\/span> \n    hr.candidates c\n    RIGHT<\/span> JOIN<\/span> hr.employees e \n        ON<\/span> e.fullname = c.fullname;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Notice that all rows from the right table (employees<\/code>) are included in the result set.<\/p>\n\n\n\n
The Venn diagram illustrates the right join of two result sets:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Similarly, you can get rows that are available only in the right table by adding a WHERE<\/a><\/code> clause to the above query as follows:<\/p>\n\n\n
SELECT<\/span>  \n    c.id candidate_id,\n    c.fullname candidate_name,\n    e.id employee_id,\n    e.fullname employee_name\nFROM<\/span> \n    hr.candidates c\n    RIGHT<\/span> JOIN<\/span> hr.employees e \n        ON<\/span> e.fullname = c.fullname\nWHERE<\/span>\n    c.id IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
And the Venn diagram that illustrates the operation:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
SQL Server full join #<\/a><\/h2>\n\n\n\n
The full outer join<\/a> or full join<\/a> returns a result set that contains all rows from both left and right tables, with the matching rows from both sides where available. In case there is no match, the missing side will have NULL<\/a> values.<\/p>\n\n\n\n
The following example shows how to perform a full join between the candidates<\/code> and employees<\/code> tables:<\/p>\n\n\n
SELECT<\/span>  \n    c.id candidate_id,\n    c.fullname candidate_name,\n    e.id employee_id,\n    e.fullname employee_name\nFROM<\/span> \n    hr.candidates c\n    FULL<\/span> JOIN<\/span> hr.employees e \n        ON<\/span> e.fullname = c.fullname;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The Venn diagram that illustrates the full outer join:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
To select rows that exist in either the left or right table, you exclude rows that are common to both tables by adding a WHERE<\/code><\/a> clause as shown in the following query:<\/p>\n\n\n
SELECT<\/span>  \n    c.id candidate_id,\n    c.fullname candidate_name,\n    e.id employee_id,\n    e.fullname employee_name\nFROM<\/span> \n    hr.candidates c\n    FULL<\/span> JOIN<\/span> hr.employees e \n        ON<\/span> e.fullname = c.fullname\nWHERE<\/span>\n    c.id IS<\/span> NULL<\/span> OR<\/span>\n    e.id IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The Venn diagram illustrates the above operation:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this tutorial, you have learned various SQL Server joins that combine data from two tables.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Third, insert<\/a> some rows into the candidates<\/code> and employees<\/code> tables:<\/p>\n\n\n
Second, create two new tables<\/a> named candidates<\/code> and employees<\/code> in the hr<\/code> schema:<\/p>\n\n\n