--- title: "JOINS in SQL Server" description: "By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft SQL Serv" author: "Anonymous User" published: 2011-07-06 updated: 2019-09-07 canonical: https://www.mindstick.com/articles/556/joins-in-sql-server category: "database" tags: ["database"] reading_time: 4 minutes --- # JOINS 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. **There are many types of [joins in SQL](https://answers.mindstick.com/qa/49636/what-is-joins-in-sql-server) server in which some important joins are described briefly below.** - [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) - CROSS JOIN ##### INNER JOIN: Inner joins returns rows when at least one match are found in both tables. That is we can say that inner join returns intersection value (common column data value) of [two tables](https://www.mindstick.com/forum/159646/how-to-join-two-tables-in-oracle-to-get-single-line-results). ##### Syntax: Using SELECT command with INNER JOIN SELECT [COLUMN_NAME] || [ALL] FROM INNER JOIN ON ##### Example: TABLE1: STUDENT_DETAIL ![JOINS in SQL Server](https://www.mindstick.com/mindstickarticle/88150490-6f81-4728-a8cb-650d3097e3fa/images/35a52390-bb16-4747-94c0-ecebcc42bd8e.png) TABLE2: STUDENT_DETAILS2 ![JOINS in SQL Serve](https://www.mindstick.com/mindstickarticle/88150490-6f81-4728-a8cb-650d3097e3fa/images/9edef595-a2b7-4c53-8c21-30fa3e448415.png) ``` ---- SELECT DATA FROM TWO TABLE WHERE TABLE ID MATCHESSELECT * FROM STUDENT_DETAIL STD1 INNER JOIN STUDENT_DETAILS2 STD2 ON STD1.id = STD2.ID ``` ##### Desired Output: ![JOINS in SQL Serve](https://www.mindstick.com/mindstickarticle/88150490-6f81-4728-a8cb-650d3097e3fa/images/0330491d-12b7-4b84-a0ec-b3ea5d3a8082.png) ##### OUTER JOIN: Outer join has three main components which are namely as follows: - LEFT OUTER JOIN - RIGHT OUTER JOIN - FULL OUTER JOIN ##### 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 values. ##### Syntax: USING SELECT COMMAND WITH LEFTOUTER JOIN SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM LEFT OUTER JOIN ON ##### Example: ``` ---- SELECT ALL DATA FROM FIRST TABLE AS WELL AS FROM SECOND  TABLE WHERE TABLE ID MATCHESSELECT * FROM STUDENT_DETAIL STD1 LEFT OUTER JOIN STUDENT_DETAILS2 STD2 ON STD1.id = STD2.ID ``` ##### Desired Output: ![JOINS in SQL Server](https://www.mindstick.com/mindstickarticle/88150490-6f81-4728-a8cb-650d3097e3fa/images/be09951a-18e9-4dbe-a81c-94c137274f3b.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](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:USING SELECT COMMAND WITH RIGHT OUTER JOIN ```                 SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM RIGHT OUTER JOIN ON ``` ##### Example: ``` ---- SELECT ALL DATA FROM SECOND TABLE AS WELL AS FROM FIRST  TABLE WHERE TABLE ID MATCHESSELECT * FROM STUDENT_DETAIL STD1 RIGHT OUTER JOIN STUDENT_DETAILS2 STD2 ON STD1.id = STD2.ID ``` ##### Desired Output: ![JOINS in SQL Server](https://www.mindstick.com/mindstickarticle/88150490-6f81-4728-a8cb-650d3097e3fa/images/a4d45ba8-6bd6-45e4-bca8-9712d0f6fd5a.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 when there is no match. **Syntax:** USING SELECT COMMAND WITH FULL OUTER JOIN SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM FULL OUTER JOIN ON ##### Example: ``` ---- SELECT ALL DATA FROM TWO TABLE IF ID NOT MATCH THEN RETURN NULL VALUESELECT * FROM STUDENT_DETAIL STD1 FULL OUTER JOIN STUDENT_DETAILS2 STD2 ON STD1.id = STD2.ID ``` ![JOINS in SQL Server](https://www.mindstick.com/mindstickarticle/88150490-6f81-4728-a8cb-650d3097e3fa/images/a6cf3af4-579f-41a8-a69f-5ad27583cb53.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 COMMAND USING WITH CROSS JOIN SELECT [COLUMN NAME | ALL COLUMN] FROM CROSS JOIN ON ``` Example:---- SELECT ALL CROSSALY JOIN DATA ASSOCIATED TO EACH OTHERSELECT * FROM STUDENT_DETAIL STD1 CROSS JOIN STUDENT_DETAILS2 STD2 ``` ![JOINS in SQL Server](https://www.mindstick.com/mindstickarticle/88150490-6f81-4728-a8cb-650d3097e3fa/images/2dbf6444-e41a-431a-a0fb-b7853bf9a735.png) ##### CORSS JOIN WITH WHERE CLAUSE: ``` ---- CROSS JOIN WITH WHERE CLAUSESELECT * FROM STUDENT_DETAIL STD1 CROSS JOIN STUDENT_DETAILS2 STD2 WHERE STD1.ID = STD2.ID ``` ![JOINS in SQL Server](https://www.mindstick.com/mindstickarticle/88150490-6f81-4728-a8cb-650d3097e3fa/images/c2314aae-8a92-4933-a3f3-24593333932a.png)\ ##### CROSS JOIN WITH WHERE CLAUSE TO SEARCH NAME ``` ---- CROSS JOIN WITH WHERE CLAUSE AND SEARCH PATTERNSELECT * FROM STUDENT_DETAIL STD1 CROSS JOIN STUDENT_DETAILS2 STD2 WHERE STD1.name LIKE 'AR%' ``` ![JOINS in SQL Server](https://www.mindstick.com/mindstickarticle/88150490-6f81-4728-a8cb-650d3097e3fa/images/74fa8b7a-bcec-4360-821d-53dfaf51bd2a.png) ##### NOTE: A WHERE clause only turns a Cross Join into an Inner Join when it provides the join criteria, as in: \ WHERE TableA.Key = TableB.Key \ --- Original Source: https://www.mindstick.com/articles/556/joins-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.