[go: up one dir, main page]
Scribd
Upload
101 views11 pages

SQL Server - Introduction To Joins - Basic of Joins: Inner Join

The document discusses different types of JOINs in SQL including: INNER JOIN, which returns rows when there is a match in both tables; OUTER JOINs including LEFT, RIGHT, and FULL OUTER JOINs, which return all rows from the left/right table along with matching rows from the right/left table; and CROSS JOIN, which performs a Cartesian product between the tables without a condition. Examples of SQL queries using each type of JOIN are provided.

Uploaded by

krris_4u
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
101 views11 pages

SQL Server - Introduction To Joins - Basic of Joins: Inner Join

The document discusses different types of JOINs in SQL including: INNER JOIN, which returns rows when there is a match in both tables; OUTER JOINs including LEFT, RIGHT, and FULL OUTER JOINs, which return all rows from the left/right table along with matching rows from the right/left table; and CROSS JOIN, which performs a Cartesian product between the tables without a condition. Examples of SQL queries using each type of JOIN are provided.

Uploaded by

krris_4u
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 11

SQL SERVER Introduction to JOINs Basic of JOINs

April 13, 2009 by pinaldave

The launch of Gandhinagar SQL Server User Group was a tremendous, astonishing success! It was overwhelming to see a large gathering of enthusiasts looking up to me (I was the Key Speaker) eager to enhance their knowledge and participate in some brainstorming discussions. Some members of User Group had requested me to write a simple article on JOINS elucidating its different types. Download SQL Script used throughout in this article to practice along. Now, take a quick look at the following two tables I have created.

INNER JOIN
This join returns rows when there is at least one match in both the tables.

OUTER JOIN
There are three different Outer Join methods. 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.

RIGHT

OUTER

JOIN

This join returns all the rows from the right table in conjunction with the matching rows from the left

table.

If

there

are

no

columns

matching

in

the

left

table,

it

returns

NULL

values.

FULL

OUTER

JOIN

This join combines left outer join and right outer join. It returns row from either table when the

conditions

are

met

and

returns

null

value

when

there

is

no

match.

CROSS JOIN
This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Additional Notes related to JOIN:


The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below. SELECT t1.* FROM Table1 WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 GO The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join. /* LEFT JOIN WHERE NULL */ t1 t2)

SELECT t1.*,t2.* FROM Table1 t1

LEFT JOIN Table2 WHERE t2.ID IS NULL

t2 ON t1.ID = t2.ID

The above example can also be created using Right Outer Join.

NOT

INNER

JOIN

Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.

You can download the complete SQL Script here, but for the sake of complicity I am including the same script here. USE AdventureWorks GO CREATE (ID INT, Value VARCHAR(10)) INSERT SELECT 1,'First' UNION ALL SELECT 2,'Second' UNION ALL SELECT 3,'Third' UNION ALL SELECT 4,'Fourth' UNION ALL SELECT 5,'Fifth' INTO Table1 (ID, Value) TABLE table1

GO CREATE (ID INT, Value VARCHAR(10)) INSERT SELECT 1,'First' UNION ALL SELECT 2,'Second' UNION ALL SELECT 3,'Third' UNION ALL SELECT 6,'Sixth' UNION ALL SELECT 7,'Seventh' UNION ALL SELECT 8,'Eighth' GO SELECT * FROM Table1 SELECT * FROM Table2 GO USE AdventureWorks GO /* SELECT t1.*,t2.* FROM Table1 INNER GO /* SELECT t1.*,t2.* FROM Table1 LEFT JOIN Table2 GO t1 t2 ON t1.ID = t2.ID LEFT JOIN */ JOIN Table2 t1 t2 ON t1.ID = t2.ID INNER JOIN */ INTO Table2 (ID, Value) TABLE table2

/* SELECT t1.*,t2.* FROM Table1 RIGHT JOIN Table2 GO /* SELECT t1.*,t2.* FROM Table1 FULL OUTER JOIN Table2 GO /* LEFT

RIGHT

JOIN

*/

t1 t2 ON t1.ID = t2.ID

OUTER

JOIN

*/

t1 t2 ON t1.ID = t2.ID

JOIN

WHERE

NULL

*/

SELECT t1.*,t2.* FROM Table1 LEFT JOIN Table2 WHERE t2.ID IS NULL GO /* RIGHT JOIN WHERE NULL */ t1 t2 ON t1.ID = t2.ID

SELECT t1.*,t2.* FROM Table1 RIGHT JOIN Table2 WHERE t1.ID IS NULL GO /* OUTER JOIN WHERE NULL */ t1 t2 ON t1.ID = t2.ID

SELECT t1.*,t2.* FROM Table1 FULL OUTER JOIN Table2 WHERE t1.ID IS NULL GO /* SELECT t1.*,t2.* FROM Table1 CROSS JOIN Table2 GO t1 t2 CROSS JOIN */ t1 t2 ON t1.ID = t2.ID OR t2.ID IS NULL

DROP DROP GO

TABLE table1 TABLE table2

You might also like