--- title: "Joins in SQL Server" description: "The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between columns on table. A JOIN is a mean" author: "Sachindra Singh" published: 2011-02-14 updated: 2019-09-07 canonical: https://www.mindstick.com/articles/450/joins-in-sql-server category: "database" tags: ["database"] reading_time: 4 minutes --- # Joins in SQL Server ##### What is Join? The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between columns on table. A JOIN is a means for combining fields from two tables by using values common to each. ##### Kinds of Join: 1. Inner join 2. Equi-join 3. Cross join 4. Outer joins a. Left outer join b. Right outer joins c. Full outer join 5. Self-join ##### Inner Join An Inner join is the most common join operation used in SQL Server and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The INNER JOIN keyword return rows when there is at least one match in both tables. I have two tables Student Detail and CourseDetail from Inner Join keyword I want to retrieve Name, City and Age from Student Detail table and Course name from CourseDetail table as shown below; ##### Student Detail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/32456785-bb62-4515-a869-0dffe4206c52.png) ##### CourseDetail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/26844572-b07b-44b4-8aa6-0f874ad626c8.png) SQL query as shown below to retrieve get data. ##### Query SELECT StudentDetail.Name, StudentDetail.City, StudentDetail.Age,CourseDetail.Course FROM StudentDetail INNER JOIN CourseDetail on StudentDetail.Id=CourseDetail.Id ##### Result ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/ccb3cef6-3134-401f-b895-30922f942602.png) ##### Equi-Join **Equi-join** returns all the columns from both tables and filters the records satisfying the matching condition as shown below: ##### Student Detail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/32456785-bb62-4515-a869-0dffe4206c52.png) ##### CourseDetail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/26844572-b07b-44b4-8aa6-0f874ad626c8.png) ##### Query SELECT * FROM StudentDetail JOIN CourseDetail ON StudentDetail.Id = CourseDetail.Id ##### Result ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/9390b4fb-ac46-471a-89ca-e93275a64e62.png) ##### Cross Join Cross join this join has a slightly different format in that it does not have an “ON” clause with a Join Condition. The CROSS join it doesn't need a join condition. What it does is perform a Cartesian product of the tables involved in the join. This mean every row in the left table is joined to every row in the right table as shown below: ##### Student Detail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/32456785-bb62-4515-a869-0dffe4206c52.png) ##### CourseDetail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/26844572-b07b-44b4-8aa6-0f874ad626c8.png) ##### Query SELECT * FROM StudentDetail cross JOIN CourseDetail ##### Result ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/c089c0e5-d290-487c-9cec-5adc89fa78a3.png) ##### Outer Joins Outer join has 3 subcategories. All 3 subcategories have a similar function. This JOINs are basically use for bring 2 tables together but include data even if there the Join Condition is does not find a matching data, it does fill NULL in the table columns. ##### Left Outer join The Left outer join logical operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The no matching rows in the second input are returned as NULL values. If no join predicate exists in the Argument column, each row is a matching row as shown below: ##### Student Detail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/32456785-bb62-4515-a869-0dffe4206c52.png) ##### CourseDetail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/26844572-b07b-44b4-8aa6-0f874ad626c8.png) ##### Query SELECT S.ID,S.Name,c.Course,c.Duration FROM StudentDetail S LEFT OUTER JOIN CourseDetail C ON S.Id = C.Id ##### Result ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/c28e095f-f0d1-436f-b8df-2e0af04941f4.png) ##### Right Outer Join The Right join keyword Return all rows from the right table (StudentDetail), even if there are no matches in the left table (CourseDetail) on the Join condition when no record is found in the opposite table NULL values are used for the columns.\ ##### Student Detail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/4e4581b1-d553-4cc2-9ace-20cf99639040.png) ##### CourseDetail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/ab7c3313-ff5a-4752-af33-6fc9969c2500.png) ##### Query SELECT S.ID,S.Name,c.Course,c.Duration FROM StudentDetail S Right OUTER JOIN CourseDetail C ON S.Id = C.Id ##### Result ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/7d5c2227-a64d-497b-a543-bc01b2ad1eb1.png) ##### Full Outer Join The **Full join** keyword return rows when there is a match in one of the tables. Full Join is a combination of both Left and Right outer join. All records from both Left table and Right table are in the result set and matched when they can be on the Join condition when no record is found in the opposite table NULL values are used for the columns as shown below: ##### Student Detail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/4e4581b1-d553-4cc2-9ace-20cf99639040.png) ##### CourseDetail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/ab7c3313-ff5a-4752-af33-6fc9969c2500.png) ##### Query SELECT S.ID,S.Name,c.Course,c.Duration FROM StudentDetail S Full OUTER JOIN CourseDetail C ON S.Id = C.Id ##### Result ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/da5cb297-781f-4c5a-935f-92aa1bfcf1f8.png) ##### Self Join Self join helps in retrieving the records having some relation or similarity with other records in the same table. ##### Student Detail table ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/4e4581b1-d553-4cc2-9ace-20cf99639040.png) ##### CourseDetail table ##### Query SELECT S.ID,S.Name,c.Course,c.Duration FROM StudentDetail S JOIN CourseDetail C ON S.Id = C.Id ##### Result ![Joins in SQL Server](https://www.mindstick.com/mindstickarticle/68230a6b-50a8-4fd5-92f9-95ec8e95365f/images/bc67a2e2-07af-46d7-a86e-e663a5c97323.png) ***You can also read these related post-*** [https://www.mindstick.com/Blog/291/join-in-sql-server](https://www.mindstick.com/Blog/291/join-in-sql-server)[https://www.mindstick.com/Articles/35/join-in-sql-server](https://www.mindstick.com/Articles/35/join-in-sql-server) \ --- Original Source: https://www.mindstick.com/articles/450/joins-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.