--- title: "Joins in SQL Server" description: "In this article, I’m trying to explain the concept of joins in SQL server and its types." author: "Sumit Kesarwani" published: 2013-05-13 updated: 2020-04-08 canonical: https://www.mindstick.com/articles/1295/joins-in-sql-server category: "database" tags: ["database"] reading_time: 4 minutes --- # Joins in SQL Server In this article, I’m trying to [explain the concept](https://www.mindstick.com/forum/158822/explain-the-concept-of-abstract-classes-and-their-significance-in-oop) of [joins in SQL](https://answers.mindstick.com/qa/49636/what-is-joins-in-sql-server) server and its types.\ Databases are usually comprised of several tables that are related to one another in some way. You need to pull the information from more than one table at a time so that you get the desired result. Joins are used to extract data from more than one table at a time and produce the information as a single result set. Join is used to link or connect tables on a common column and return the record that match in those columns. In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use [primary key](https://www.mindstick.com/blog/474/remove-primary-key-from-access-table) of first table and [foreign key](https://www.mindstick.com/blog/174/foreign-key-self-reference-constraint) of secondary table to get data from tables by using this relationship we can reduce the duplication of data in every table. Before enter into Joins concept first design two tables in database and enter data like as shown below ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/cd69571b-bf8d-49c0-a88a-55f151904d2b/images/6239a3c6-7f5b-4c76-bab2-c1747a82971e.png) Give the table name as CustomerTable. Here CUSTID is the primary key. ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/cd69571b-bf8d-49c0-a88a-55f151904d2b/images/585e1456-94ed-4224-999a-f908e140095e.png) Give the table name as ProductTable. Here PRODUCTID is the primary key and CUSTID is the foreign key. ##### Types of Joins - [Inner Join](https://www.mindstick.com/blog/304465/difference-between-inner-join-left-join-right-join-and-full-outer-join-in-sql-server) - [Outer Join](https://www.mindstick.com/forum/33754/how-to-use-sql-full-outer-join) - Self Join ##### Inner Join The join that displays only the rows that have a match in both the joined tables is known as inner join. This is default join in the query and view designer. ##### Syntax: ``` SELECT T1.ColumnName, T2.ColumnNameFROM TableName1 T1INNER JOIN TableName2 T2ON T1.ColumnName=T2.ColumnName ``` Example ``` SELECT C.CUSTNAME,C.ADDRESS,P.PRODUCTNAME,P.PRICEFROM CustomerTable CINNER JOIN ProductTable PON C.CUSTID=P.CUSTID ``` ##### Output ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/cd69571b-bf8d-49c0-a88a-55f151904d2b/images/49fe55e1-b614-406b-ba10-e28e0755e474.png) Types of Inner Join - Equi Join - Natural Join - Cross Join ##### Equi Join In an equi join, column values are compared for equality and even the duplicate columns are displayed. ##### Syntax: ``` SELECT * FROM TableName1 T1Join TableName2 T2ON T1.ColumnName=T2.ColumnName ``` Example ``` SELECT * FROM CustomerTable CJoin ProductTable PON C.CUSTID=P.CUSTID ``` ##### Output ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/cd69571b-bf8d-49c0-a88a-55f151904d2b/images/a8e7a419-02db-4cef-ac4d-64285790df8d.png) Natural Join In natural join, the duplicate columns are not there. Thus, when you join the two tables, you can select all the fields from one table and specify the fields that you want from the other table. ##### Syntax: SELECT * FROM TableName1 NATURAL JOIN TableName2 ##### Example ``` SELECT * FROM CustomerTable NATURAL Join ProductTable ``` Natural Joins won’t work in [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server)(only supports in Oracle) it will throw a [syntax error](https://www.mindstick.com/forum/159599/what-is-a-syntax-error-in-sql-give-an-example). ##### Cross Join A [cross join](https://www.mindstick.com/interview/1850/what-is-cross-join) produces the Cartesian product of the tables those involved in the join. The size of the Cartesian product is the number of the rows in the first table multiplied by the number of the rows in the second table. ##### Syntax: ``` SELECT * FROM TableName1CROSS JOIN TableName2 ``` Example ``` SELECT * FROM CustomerTable CROSS JOIN ProductTable ``` ##### Output ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/cd69571b-bf8d-49c0-a88a-55f151904d2b/images/36a01f87-2961-4c00-9abd-e2553afbda5f.png) Outer Join Outer joins restrict rows from one table while allowing all rows from the second table as a result set. ##### There are three types of outer joins: - Left Outer Join - Right Outer Join - Full Outer Join ##### Left Outer Join It includes all the rows from the first table and only the matching rows from the second table. ``` Syntax:SELECT ColumnName FROM TableName1 T1LEFT OUTER JOIN TableName2 T2ON T1.ColumnName=T2.ColumnName ``` Example ``` SELECT C.CUSTID,C.CUSTNAME,P.PRODUCTNAME FROM CustomerTable CLEFT OUTER JOIN ProductTable PON C.CUSTID=P.CUSTID ``` ##### Output ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/cd69571b-bf8d-49c0-a88a-55f151904d2b/images/1c09dcba-8119-45aa-947a-889861001388.png) Right Outer Join It includes all the rows from the second table and only the matching rows from the table. ##### Syntax: ``` SELECT ColumnName FROM TableName1 T1RIGHT OUTER JOIN TableName2 T2ON T1.ColumnName=T2.ColumnName ``` Example ``` SELECT C.CUSTID,C.CUSTNAME,P.PRODUCTNAME,P.PRICE FROM CustomerTable CRIGHT OUTER JOIN ProductTable PON C.CUSTID=P.CUSTID   ``` ##### Output ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/cd69571b-bf8d-49c0-a88a-55f151904d2b/images/b46b26ce-b31e-46de-8061-1c7831e85010.png) Full Outer Join It includes all the rows, matching as well as non-matching. ##### Syntax: ``` SELECT ColumnName FROM TableName1 T1FULL OUTER JOIN TableName2 T2ON T1.ColumnName=T2.ColumnName ``` Example ``` SELECT C.CUSTID,C.CUSTNAME,P.PRODUCTNAME,P.PRICE FROM CustomerTable CFULL OUTER JOIN ProductTable PON C.CUSTID=P.CUSTID ``` ##### Output ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/cd69571b-bf8d-49c0-a88a-55f151904d2b/images/b1fdc061-9231-413d-b121-ec10d9d96d7e.png) Self Join Self join correlates rows of a table with the other rows of the same table. It is used when a table has to be joined to itself to produce results. ##### Example First you have to create a table EmpTable and enter the details like this: ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/cd69571b-bf8d-49c0-a88a-55f151904d2b/images/69082248-dc27-416e-a9f9-90887a407dce.png) ``` SELECT E2.EMPNAME,E1.EMPNAME as 'MANAGER'FROM EmpTable E1INNER JOIN EmpTable E2ON E1.EMPID=E2.EMPMGRID ``` ##### Output ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/cd69571b-bf8d-49c0-a88a-55f151904d2b/images/c9d557a2-df1e-4b97-b2ea-3de0fcc1a2ca.png) --- Original Source: https://www.mindstick.com/articles/1295/joins-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.