Sunday 17 April 2011

Joins In Sql

What is a Join in SQL Server?

Join is used  to put data from two or more tables into a single result set.

What are the types of Joins that we can have with Sql Server?

There are 4 types of joins: Inner Join, Outer Join, Cross Join , Self Join

INNER JOIN

Inner join shows matches only when they exist in both tables.Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers Customerid and Orders Customerid.So this SQL will only give you result with customers who have orders.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

LEFT OUTER JOIN:

Left join will display all records in left table of the SQL statement whether their is a matching record in right table  or not. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values.

SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

RIGHT OUTER JOIN:

Right join will display all records in right table of the SQL statement whether their is matching record in left table or not. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values.

SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

CROSS JOIN:

It will return the cartesian product of rows of both the tables. Suppose their is 3 rows in first table and 5 in second able then it will return total of 15 rows.

SELECT Customers.*, Orders.* FROM Customers CROSS JOIN Orders ON Customers.CustomerID =Orders.CustomerID

                                                                  1  3

No comments:

Post a Comment