First of all take a look on the names of different types of joins in SQL.
1. Inner Join
- Self Join
- Equi Join
- Natural Join
2. Outer Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
3. Cross Join
Joins in SQL Server allows the retrieval of data records from one or more tables having some relation between them. Logical operators can also be used to drill down the number of records to get the desired output from sql join queries.
1. Inner Join: Inner Join is a default type join of SQL Server. It uses logical operators such as =, <, > to match the records in two tables. Inner Join includes equi join and natural joins.
Examples:
SQL Inner Join Examples
SQL Inner Natural Join Examples
SQL Inner Equi Join Examples
2. Outer Join: Outer Join has further 3 sub categories as left, right and full. Outer Join uses these category names as keywords that can be specified in the FROM clause.
o Left Outer Join: Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. If in the left table any row has no matching record in the right side table then that row returns null column values for that particular tuple.
Examples:
SQL Left Outer Join Examples
o Right Outer Join: Right Outer Join is exactly the reverse method of Left Outer Join. It returns all the rows from right table and returns null values for the rows having no match in the left joined table.
Examples:
SQL Right Outer Join Examples
o Full Outer Join: Full outer join returns all the rows from both left and right joined tables. If there is any match missing from the left table then it returns null column values for left side table and if there is any match missing from right table then it returns null value columns for the right side table.
Examples:
SQL Full Outer Join Examples
3. Cross Join: Cross join works as a Cartesian product of rows for both left and right table. It combined each row of left table with all the rows of right table.
Examples:
SQL Cross Join Cartesian product Examples
SQL Inner Join Examples
3 Different types of SQL Server Inner Join Examples:
1 Self Join
2 Equi Join (inner Join)
3 Natural Join(inner Join)
Try the example of joining the single table to itself. Self join in sql server 2000/2005.
Equi Join (Inner Join) for combining the columns of two sql database tables into single table output result. Try sql inner equi join examples.
Natural Join (Inner Join) for getting the output of equi join into the specified columns format and removing the ambiguous column names from the output. See sql inner natural join examples.
SQL Inner Join query with (=) operator:
SELECT C.CATEGORYID, C.CATEGORYNAME, P.PRODUCTID, P.PRODUCTNAME, P.UNITPRICE
FROM CATEGORIES C INNER JOIN
PRODUCTS P ON P.CATEGORYID = C.CATEGORYID
WHERE P.UNITPRICE = 10
ORDER BY C.CATEGORYNAME, P.PRODUCTNAME
This inner join query will return the categoryid, categoryname, productid, productname, unitprice where product unit price = 10
SQL Inner Join Query with (>) operator:
SELECT DISTINCT C.CATEGORYID, C.CATEGORYNAME
FROM CATEGORIES C INNER JOIN
PRODUCTS P ON C.CATEGORYID > P.CATEGORYID
WHERE P.UNITPRICE = 10
ORDER BY C.CATEGORYNAME
This inner join query will return the categoryId, categoryName having products with unit price=10
SQL Inner Join Query with not equal (<>) operator:
SELECT DISTINCT P1.PRODUCTNAME, P1.UNITPRICE, P1.SUPPLIERID
FROM PRODUCTS P1 INNER JOIN PRODUCTS P2
ON
P1.SUPPLIERID=P2.SUPPLIERID
AND P1.UNITPRICE<>P2.UNITPRICE
WHERE P1.UNITPRICE < 20 AND P2.UNITPRICE < 20
ORDER BY P1.SUPPLIERID
Inner Join with not equal operator is rarely used in self joins. As an example above sql self join query returns the productname, unitprice, supplierid where suppliers having 2 or more than 2 products with unit price less than 20.
Self Join In Sql Server 2000 2005
Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause.
You can see the example of Joins using table aliases in SQL Server 2005 here.
For self join in sql you can try the following example:
Create table employees:
emp_id emp_name emp_manager_id
1 John Null
2 Tom 1
3 Smith 1
4 Albert 2
5 David 2
6 Murphy 5
7 Petra 5
Now to get the names of managers from the above single table you can use sub queries or simply the self join.
Self Join SQL Query to get the names of manager and employees:
select e1.emp_name ‘manager’,e2.emp_name ’employee’
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id
Result:
manager employee
John Tom
John Smith
Tom Albert
Tom David
David Murphy
David Petra
Understanding the Self Join Example
In the above self join query, employees table is joined with itself usingtable aliases e1 and e2. This creates the two views of a single table.
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id
Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.
SQL Inner Equi Join Examples
Inner join is a default type of SQL Join that return the records matching in all the tables joined in sql query satisfying the condition specified in WHERE clause.
Inner join includes 3 types of joins similar to one another.
Self Join: Self join joins a single sql database table to itself. You can see the example of self join here … Self Join In Sql Server 2000 2005
Equi Join: Equi Join returns all the columns from both tables and filters the records satisfying the matching condition specified in Join “ON”statement of sql inner join query.
SQL Inner Equi Join Example:
USE NORTHWIND
SELECT * FROM CATEGORIES C INNER JOIN
PRODUCTS P ON P.CATEGORYID = C.CATEGORYID
Result will display the following columns:
CategoryID, CategoryName, Description, Picture, ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
Above equi join sql query will display the categoryId two times in a row because both the tables have categoryId column. You can convert the result into natural join by elimination the identical columns and unnecessary columns.
SQL Inner Natural Join Examples
In the previous article you learnt how to use equi inner join in sql queries to join two tables and retrieve the combined result of both sql database tables. See the example of equi join here… SQL Inner Equi Join Examples. Notice that equi join sql query returned the categoryId column twice because of relation between two tables. Products table also has categoryId column that shows the product belongs to a particular category in categories whose categoryId is also saved in products table as a relational key between both the tables.
You can specify the required column names using sql natural join query.
Natural join query example:
SELECT C.*, P.PRODUCTID, P.PRODUCTNAME FROM CATEGORIES C
INNER JOIN
PRODUCTS P ON P.CATEGORYID = C.CATEGORYID
This natural join query will return all the columns of categories table and prodcutId and productName from products table.
You can further modify this natural inner join query as per your requirements to visualize the data by specifying the column names of categories table also.
Inner Join Query Example by specifying column names:
SELECT C.CATEGORYID, C.CATEGORYNAME, P.PRODUCTID, P.PRODUCTNAME, P.UNITPRICE FROM CATEGORIES C INNER JOIN
PRODUCTS P ON P.CATEGORYID = C.CATEGORYID
This inner join query will display only the specified column names of both the tables.
SQL Left Outer Join Examples
Inner joins return only those rows from both sql database tables having matching records in both the tables whereas left outer join returns all the rows from the left table and related matching records from the other one.
SQL Left Outer Join Example:
USE PUBS
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A LEFT OUTER JOIN PUBLISHERS P
ON A.CITY = P.CITY
ORDER BY A.AU_LNAME, A.AU_FNAME
Result:
au_fname au_lname pub_name
Abraham Bennet Algodata Infosystems
Reginald Blotchet-Halls NULL
Cheryl Carson Algodata Infosystems
Michel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Heather McBadden NULL
Michael O’Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
This left outer join query retrieves the author names and publisher name having same cities. Here all rows retrieved from the left table i.e. authors and publisher name having the similar city other columns of pub_name column are null due to no match found in the right table.
SQL Right Outer Join Examples
In the previous article regarding sql left outer join we learnt left outer join that retrieves all the results from left table and related matches from the right table where right table having no matches displays the Null value in the corresponding columns. Consider the same example of authors and publishers table of the existing database PUBS of sql server 2000.
We used the following left outer join query:
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A LEFT OUTER JOIN PUBLISHERS P
ON A.CITY = P.CITY
ORDER BY A.AU_LNAME, A.AU_FNAME
Just change the left keyword to right outer join in above example; you will get the reverse output of left outer join in the form of right outer join.
SQL Right Outer Join query Example:
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A RIGHT OUTER JOIN PUBLISHERS P
ON A.CITY = P.CITY
ORDER BY A.AU_LNAME, A.AU_FNAME
Result:
au_fname au_lname pub_name
NULL NULL New Moon Books
NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing
NULL NULL Ramona Publishers
NULL NULL GGG&G
NULL NULL Scootney Books
NULL NULL Lucerne Publishing
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
Notice the difference in the output of right outer join and left outer join. Right outer join returned all the rows from right table as all publisher names and null values for the left table columns having no match found in left table’s au_fname and au_lname.
SQL Full Outer Join Examples
To retrieve all the records from left as well as right table unless the records have matching relations in each row you can use SQL FULL OUTER JOIN.
You can consider the examples of last two articles about left outer joinand right outer join, in which left outer join retrieves all records from the left table and as all records of right table in right outer join along with null values for the columns having no matching records in any tuple. To retain all the records of left as well as right table along with null values for non matching rows displaying the combination of results of left outer and right outer join, FULL OUTER JOIN is the best solution.
SQL FULL outer join example:
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A FULL OUTER JOIN PUBLISHERS P
ON A.CITY = P.CITY
ORDER BY A.AU_LNAME, A.AU_FNAME
Result:
au_fname au_lname pub_name
NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing
NULL NULL New Moon Books
NULL NULL Lucerne Publishing
NULL NULL Scootney Books
NULL NULL Ramona Publishers
NULL NULL GGG&G
Abraham Bennet Algodata Infosystems
Reginald Blotchet-Halls NULL
Cheryl Carson Algodata Infosystems
Michel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Heather McBadden NULL
Michael O’Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Above output retrieved from the sql full outer join query is the exact combination of both the left as well as right join outputs.
Also learn other types of SQL Outer Join:
SQL Left Outer Join Examples
SQL Right Outer Join Examples
SQL Cross Join Cartesian product Examples
SQL Cross join returns the output result as a Cartesian product of both database tables.
Let left table has 10 rows and right table has 8 rows then SQL CROSS Join will return 180 rows combining each record of left table with all records of right side table. Consider the following example of CROSS Join:
USE PUBS
SELECT AU_FNAME, AU_LNAME, PUB_NAME
FROM AUTHORS CROSS JOIN PUBLISHERS
ORDER BY AU_FNAME
Above cross join will return 23 * 8 = 184 results by multiplying each row of authors table with publishers table.
SQL CROSS Join with WHERE clause
By just adding the where clause with Cross join sql query it turns the output result into inner join.
Example:
USE PUBS
SELECT AU_FNAME, AU_LNAME, PUB_NAME
FROM AUTHORS CROSS JOIN PUBLISHERS
WHERE AUTHORS.CITY = PUBLISHERS.CITY
ORDER BY AU_FNAME
It will display only the matching results in both tables.
Result:
au_fname au_lname pub_name
Cheryl Carson Algodata Infosystems
Abraham Bennet Algodata Infosystems
Learn different types of Joins in SQL:
SQL Left Outer Join Examples
SQL Right Outer Join Examples
SQL Full Outer Join Examples
SQL Inner Join Examples