Full Outer join and Cross Join in SQL Server 2008

Description:

In this article I describe the purpose of full outer join and cross join.

Content: In the full outer join which includes all rows from both tables, regardless of whether or not the other table has a matching value. That means it is a combination of left and right outer join. Here we can see the null value from any rows in any tables.

In the Left outer join generally we see all records from the left table and the matching record from the right table. In the right outer join it is vice versa.

So if you want to see both records then you will use the full outer join.

In the cross join it does not have a WHERE clause. It is the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table (m*n).

For e.g. suppose you have a table with 5 records and you have another table with 6 records.

So when you cross join the two tables it will show you 30 rows (5*6) records from the two tables.

Now below I am giving an exampl:

We have 2 tables just like in Figure 1:

1. customer(custid(pk),Custname,amount)
2. tblOrder(ordereid(pk),ordername,custid(fk))

Figure 1

Step 1:

Now run these 2 queries first:

select * from Customer
select * from tblOrder

The result will be like as shown in Figure 2:



Figure 2:

Now to see the cross join execute this script:

select * from Customer cross join tblOrder

It will execute the result like as shown in Figure 3:



Figure 3:

Now see there are 35 records showing because we have "customer" table which has 7 records and "tblorders" which has 5 records. Cross join means Cartesian products (7*5=35) records. So 35 times the record will show you with null value.

If you give the where clause then a cross join will act like an inner join. Now see the script below:

select * from Customer cross join tblOrder where Customer.CustId=tblOrder.CustId

When you run the script it will look like Figure 4:



Figure 4:

You see that the 4 records which are common in both customer and order table are displayed.

Now we will see the full outer join.

Run the script below:

select cs.CustomerName,cs.Amount,ord.OrderName  from Customer cs full outer join tblorder ord
on cs.CustId=ord.CustID
or you can write another way also:

select cs.CustomerName,cs.Amount,ord.OrderName  from Customer cs left outer join tblorder ord
on cs.CustId=ord.CustID
union
select cs.CustomerName,cs.Amount,ord.OrderName  from Customer cs right outer join tblorder ord
on cs.CustId=ord.CustID

Here you see that I have a union of the left outer and right outer join. That means it will act like a full outer join. A full outer join is nothing but a combination of reft and right outer joins.

Now when you execute the script it will look like the Figure 5.



Figure 5:

See here all the records of the two tables are displayed including the null value.

So the summation of all the scripts are

select * from Customer
select * from tblOrder
 select * from Customer cross join tblOrder
select * from Customer cross join tblOrder where Customer.CustId=tblOrder.CustId
select cs.CustomerName,cs.Amount,ord.OrderName  from Customer cs left outer join tblorder ord
on cs.CustId=ord.CustID

select cs.CustomerName,cs.Amount,ord.OrderName  from Customer cs full outer join tblorder ord
on cs.CustId=ord.CustID

select cs.CustomerName,cs.Amount,ord.OrderName  from Customer cs left outer join tblorder ord
on cs.CustId=ord.CustID
union
select cs.CustomerName,cs.Amount,ord.OrderName  from Customer cs right outer join tblorder ord
on cs.CustId=ord.CustID

Conclusion:

So that's it. In my next article I will throw light on left and right outer join. I have attached the script for generating the database and table. You have to run the script and feed the two table values like Figure 1. Or you can feed your own values also. After that run the script and see the practical result.