--- title: "SQL Joins in SQL Server" description: "A SQL join is combines records from two or more tables in a relational database by using values common to each." author: "Anupam Mishra" published: 2016-02-29 updated: 2019-09-07 canonical: https://www.mindstick.com/articles/11997/sql-joins-in-sql-server category: "database" tags: ["mssql server", "join", "sql"] reading_time: 3 minutes --- # SQL Joins in SQL Server A SQL join is combines records from two or more tables in a [relational database](https://www.mindstick.com/forum/159659/when-use-a-nosql-database-instead-of-a-relational-database-is-it-okay-to-use-both-on-the-same-site) by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join. A programmer writes a JOIN statement to identify records for joining. If the evaluated predicate is true, the combined record is then produced in the expected format, a record set or a [temporary table](https://www.mindstick.com/forum/158395/explain-the-difference-between-a-temporary-table-and-a-regular-table-in-sqlite). **Why we used SQL Join :** \ **1**. If we want to [access data](https://www.mindstick.com/forum/159853/how-to-access-data-using-entity-framework-in-asp-dot-net-mvc) more than one table with the help of a [select statement](https://www.mindstick.com/forum/160076/explain-the-sql-select-statement-and-how-it-s-used-to-search-for-data-in-a-database). \ **2**. If we want to combines rows of that tables in one table and one can retrieve the information with the help of a SELECT statement. \ **3**. If we want to joining of two or more tables is only based on common field between them. \ **SQL [INNER JOIN](https://www.mindstick.com/blog/304465/difference-between-inner-join-left-join-right-join-and-full-outer-join-in-sql-server)** is a simple join is the most common type of join. We have Two tables Employee and Department. Their Structure with data is below: \ **\** ![SQL Joins](https://www.mindstick.com/mindstickarticle/8f1b2dc1-1c5f-4ebc-9045-88ce08b3f38b/images/7f921a0b-7ba2-4d8d-8ad8-791d7961ada0.png)![SQL Joins](https://www.mindstick.com/mindstickarticle/8f1b2dc1-1c5f-4ebc-9045-88ce08b3f38b/images/36f29bcb-30df-4ff1-bc8e-fbc0e4fbc3ff.png) **\** **\** **SQL** **Inner Join:** \ A Simpler join is a SQL Inner [Join Syntax](https://www.mindstick.com/forum/159328/mysql-outer-join-syntax-error) is as follows:\ \ ![SQL Joins](https://www.mindstick.com/mindstickarticle/8f1b2dc1-1c5f-4ebc-9045-88ce08b3f38b/images/d1c41688-e3ea-4aca-927a-0b322aa7a9c5.png)\ \ **SQL Outer Join:** \ In the SQL outer JOIN all the content of the both tables are integrated together either they are matched or not. ## Outer join of two types:\ **1**.Left outer join ( Left Join): this join returns all the rows from left table combine with the matching rows of the right table. If you get no 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.\ **2**.Right outer join (Right Join): this join returns all the rows from right table are combined with the matching rows of left table .If you get no column matching in the left table .it returns null value.\ **1**.Left outer join ( Left Join):\ \ ![SQL Joins](https://www.mindstick.com/mindstickarticle/8f1b2dc1-1c5f-4ebc-9045-88ce08b3f38b/images/e6f2a4fb-2d5e-4f5a-a2af-6fcba9089cb5.png)\ **\** **Right outer join** (**Right Join**):\ \ \ ![SQL Joins](https://www.mindstick.com/mindstickarticle/8f1b2dc1-1c5f-4ebc-9045-88ce08b3f38b/images/103db7be-b150-4daf-9fd1-bbdfa3a42c26.png)\ **\** **SQL FULL JOIN:** **\** **SQL [full outer join](https://www.mindstick.com/forum/1216/full-outer-join-on-2-data-tables-with-a-list-of-columns) and SQL join are same.** **\** The SQL full join is the **result of combination of both left and right outer join and the join tables have all the records from both tables**. **It puts NULL on the place of matches not found.** Generally it is known as SQL FULL JOIN. \ ![SQL Joins](https://www.mindstick.com/mindstickarticle/8f1b2dc1-1c5f-4ebc-9045-88ce08b3f38b/images/cb0ebcaf-836d-419b-b2cf-68449e9662cb.png) **SQL CROSS JOIN:**\ **\** When we want to **combine each row of both two tables the this types join is known as Cartesian join or cross join.** In general words we can say that SQL CROSS JOIN returns the Cartesian product of the sets of rows from the joined table. We can define SQL cross join in two ways : \ 1. Using the JOIN syntax. \ 2. the table in the FROM clause without using a [WHERE clause](https://www.mindstick.com/interview/1909/when-do-you-use-where-clause-and-when-do-you-use-having-clause). \ \ ![SQL Joins](https://www.mindstick.com/mindstickarticle/8f1b2dc1-1c5f-4ebc-9045-88ce08b3f38b/images/38558aad-cc00-4920-b447-33769b530a23.png)\ \ \ --- Original Source: https://www.mindstick.com/articles/11997/sql-joins-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.