--- title: "JOIN in SQL Server" description: "By using joins, you can retrieve data from two or more tables based on logical relationships between the tables." author: "AVADHESH PATEL" published: 2012-08-18 updated: 2019-09-07 canonical: https://www.mindstick.com/articles/981/join-in-sql-server category: "database" tags: ["database"] reading_time: 4 minutes --- # JOIN in SQL Server By using **joins**, you can [retrieve data](https://www.mindstick.com/forum/159649/how-to-retrieve-data-from-object-tables-on-oracle) from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server) should use data from one table to select the rows in another table. SQL joins are used to [query data](https://www.mindstick.com/forum/914/facebook-cronjob-login-and-query-data) from two or more tables, based on a relationship between certain columns in these tables. JOIN keyword used in an SQL statement to query data more than one table.\ Type of Join 1. Self Join 2. Join or Equi Join or [Inner Join](https://www.mindstick.com/blog/304465/difference-between-inner-join-left-join-right-join-and-full-outer-join-in-sql-server) 3. Outer Join 4. Cross Join For Demonstration I’m created two tables. ``` -- CTREATED TABLE 1CREATE TABLE table1([ID] INT,[Value] VARCHAR(10))--INSERT VALUES INTO TABLE 1INSERT INTO Table1 (ID, Value)SELECT 1,'First'UNION ALLSELECT 2,'Second'UNION ALLSELECT 3,'Third'UNION ALLSELECT 4,'Fourth'UNION ALLSELECT 5,'Fifth' ``` ##### Screen Shot of Table1 ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/a8d940f1-ee78-4f2d-b3cb-278266bca375.png) ``` -- CREATE TABLE 2CREATE TABLE table2([ID] INT,[Value] VARCHAR(10)) --INSERT VALUES INTO TABLE 2INSERT INTO Table2 (ID, Value)SELECT 1,'First'UNION ALLSELECT 2,'Second'UNION ALLSELECT 3,'Third'UNION ALLSELECT 6,'Sixth'UNION ALLSELECT 7,'Seventh'UNION ALLSELECT 8,'Eighth' ``` ##### Screen Shot of Table1 ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/78e50f93-62bb-4814-809f-cb348aadca9b.png) ##### SELF JOIN A **SELF JOIN** is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the *same column in the same table*. ##### Syntax ``` SELECT [COLUMN_NAME] || [ALL] FROM  ,   WHERE   ``` ##### Example ``` /* Self Join*/SELECT t1.ID, t1.[Value] FROM Table1 t1, Table1 t2 WHERE t1.ID = t2.ID ``` ##### Screen Shot of Self Join ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/a8d940f1-ee78-4f2d-b3cb-278266bca375.png) ##### INNER JOIN INNER JOIN OR EQUI JOIN OR JOIN returns rows when there is at least one match in both the tables. ##### Syntax ``` SELECT [COLUMN_NAME] || [ALL] FROM INNER JOIN ON ``` ##### Example ``` /* JOIN OR EQUI JOIN OR INNER JOIN */SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.ID = t2.ID ``` ##### Screen Shot of Inner Join ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/54274555-a54d-4c12-94de-07cb294dbc82.png) ##### OUTER JOIN ##### There are three different Outer Join methods. I. LEFT OUTER JOIN II. RIGHT OUTER JOIN III. [FULL OUTER JOIN](https://www.mindstick.com/forum/1216/full-outer-join-on-2-data-tables-with-a-list-of-columns) ##### LEFT OUTER JOIN: This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it [returns NULL](https://www.mindstick.com/forum/159370/sql-join-returns-null-records-when-there-is-no-data-in-the-database-in-sql-server-why) values. ##### Syntax: ``` SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM LEFT OUTER JOIN ON ``` ##### Example ``` /* LEFT OUTER JOIN */SELECT * FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID ``` ##### Screen Shot of Left Outer Join ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/2b206e3b-1ef2-4a4d-9d3c-da537a5360af.png) ##### RIGHT OUTER JOIN: This join returns all the rows from the right table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values. ##### Syntax: ``` SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM RIGHT OUTER JOIN ON ``` ##### Example ``` /* RIGHT OUTER JOIN */SELECT * FROM Table1 t1 RIGHT JOIN Table2 t2 ON t1.ID = t2.ID ``` ##### Screen Shot of Right Outer Join ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/638ca5bd-dfa7-47ca-b3f4-e6ad340937f6.png) ##### FULL OUTER JOIN: This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns [null value](https://www.mindstick.com/forum/23045/document-body-appendchild-has-a-null-value) when there is no match. ##### Syntax: ``` SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM FULL OUTER JOIN ON ``` ##### Example ``` /* FULL OUTER JOIN */SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID ``` Screen Shot of Full Outer Join ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/a7522375-9201-49a2-bd18-6f62c381de99.png) ##### CROSS JOIN: This join is a Cartesian join that does not necessitate any condition to join. The result set contains records that are multiplication of record number from both the tables. That is every element of column of first table is associated with every element of column of second table. ##### Syntax: ``` SELECT [COLUMN NAME | ALL COLUMN] FROM CROSS JOIN ON ``` ##### Example ``` /* CROSS JOIN */SELECT * FROM Table1 t1 CROSS JOIN Table2 t2 ``` Screen Shot of Cross Join ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/b82309ed-0b40-4a1e-9205-294acbf26ee6.png) ##### Using OUTER JOIN and WHERE clause in Join This is [return NULL](https://www.mindstick.com/forum/34356/facebook-email-field-return-null-even-if-the-email-permission-is-set-and-accepted) column values. ##### LEFT OUTER JOIN with WHERE NULL Return left table’s [NULL values](https://www.mindstick.com/forum/159818/how-to-use-the-coalesce-function-to-handle-null-values-in-sql-queries) which not common in right table. ``` /* LEFT OUTER JOIN - WHERE NULL */SELECT * FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t2.ID IS NULL ``` ##### Screen Shot ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/4179f835-6ad6-4713-b8d6-2b0013621329.png) RIGHT OUTER JOIN with WHERE NULL Return right table’s NULL values which not common in left table. ``` /* RIGHT OUTER JOIN - WHERE NULL */SELECT * FROM Table1 t1 RIGHT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t1.ID IS NULL ``` Screen Shot ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/c0e7cee2-7d7d-463c-abfa-6b67bc19e095.png) ##### FULL OUTER JOIN with WHERE NULL Return NULL values from left and right table which not common. ``` /* FULL OUTER JOIN - WHERE NULL */SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID WHERE t1.ID IS NULL OR t2.ID IS NULL ``` ##### Screen Shot ![JOIN in SQL Server](https://www.mindstick.com/mindstickarticle/cb1067ec-69ac-41d0-9c2f-8548678196e8/images/df8f215e-7115-4267-ae66-d3b4bf79f3d9.png) \ --- Original Source: https://www.mindstick.com/articles/981/join-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.