--- title: "Join in SQL Server" description: "JOIN is used whenever we have to select data from two or more tables. To be able to use JOIN to extract data from we need a relationship between certa" author: "Anonymous User" published: 2010-07-19 updated: 2019-09-07 canonical: https://www.mindstick.com/articles/35/join-in-sql-server category: "database" tags: ["database"] reading_time: 3 minutes --- # Join in SQL Server JOIN is used whenever we have to select data from two or more tables. To be able to use JOIN to extract data from we need a relationship between certain columns in the tables. Table Used in this Article ##### Doctors ID Name ----------- -------------------- 1 Joe Manners 2 Sue Tongs 3 Jeff Spine 4 Mary Rasch 5 Tom Thumb 6 Norm Lobe ##### Patients ID Name DocID ----------- -------------------- ----------- 1 Jim Thick 4 2 Tom Small 2 3 Al Downs 4 4 Ann Hills 1 5 Tim Burrow 3 6 Jane Fern 5 7 Sam Broom 2 8 Gary Far 1 9 Bill Out 5 10 Dave Bell 4 11 Fred Overs 5 12 Greg Double 1 13 Bob Marks 9 ##### INNER JOIN An INNER JOIN is the most common join operation used in applications, and represents 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 query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. **Example** ``` select d.name as DocName, p.name as PatientNamefrom Doctors d inner join Patients p on d.ID=p.DocID ``` ![Join in SQL Server](https://www.mindstick.com/mindstickarticle/32e0f4c1-7fd1-4c86-b3ec-4dc86037045d/images/6aa9d501-4324-40d9-9790-679762643e2c.png) ##### OUTER JOIN An OUTER JOIN does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. There are three types of OUTER JOIN. - LEFT OUTER JOIN - RIGHT OUTER JOIN - FULL OUTER JOIN ##### LEFT OUTER JOIN The result of a LEFT OUTER JOIN (or simply LEFT JOIN) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table. **Example** ``` select d.name as DocName, p.name as PatientNamefrom Doctors d LEFT OUTER JOIN Patients pon d.ID=p.DocID ``` ![Join in SQL Server](https://www.mindstick.com/mindstickarticle/32e0f4c1-7fd1-4c86-b3ec-4dc86037045d/images/60f262d5-53c1-42da-a498-1e965fb0cf78.png) RIGHT OUTER JOIN A RIGHT OUTER JOIN (or RIGHT JOIN) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate). **Example** ``` select d.name as DocName, p.name as PatientNamefrom Doctors d RIGHT OUTER JOIN Patients pon d.ID=p.DocID ``` ![Join in SQL Server](https://www.mindstick.com/mindstickarticle/32e0f4c1-7fd1-4c86-b3ec-4dc86037045d/images/7e3fd0a6-34a0-4250-ad5d-89b0c67ec911.png) ##### FULL OUTER JOIN A FULL OUTER JOIN combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side **Example** ``` select d.name as DocName, p.name as PatientNamefrom Doctors d FULL OUTER JOIN Patients pon d.ID=p.DocID ``` ![Join in SQL Server](https://www.mindstick.com/mindstickarticle/32e0f4c1-7fd1-4c86-b3ec-4dc86037045d/images/d703cca3-2ea7-497b-8182-39b642a76110.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/450/joins-in-sql-server](https://www.mindstick.com/Articles/450/joins-in-sql-server) --- Original Source: https://www.mindstick.com/articles/35/join-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.