Post
Ask Question

Full Outer join and Cross Join in SQL Server

Full Outer Join and Cross Join in SQL Server

 
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 example:
 
We have 2 tables just like in Figure 1:
  1. customer(custid(pk),Custname,amount)
  2. tblOrder(ordereid(pk),ordername,custid(fk))
Join1.gif 
Figure 1
 
Step 1
 
Now run these 2 queries first:
  1. select * from Customer  
  2. select * from tblOrder  
The result will be like as shown in Figure 2:
 
Join2.gif 
Figure 2
 
Now to see the cross join execute this script:
  1. select * from Customer cross join tblOrder  
It will execute the result like as shown in Figure 3:
 
Join3.gif 
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:
  1. select * from Customer cross join tblOrder where Customer.CustId=tblOrder.CustId  
When you run the script it will look like Figure 4:
 
Join4.gif 
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:
  1. select cs.CustomerName,cs.Amount,ord.OrderName   
  2. from Customer cs full outer join tblorder ord   
  3. on cs.CustId=ord.CustID    
or you can write another way also:
  1. select cs.CustomerName,cs.Amount,ord.OrderName   
  2. from Customer cs left outer join tblorder ord   
  3. on cs.CustId=ord.CustID  
  4. union   
  5. select cs.CustomerName,cs.Amount,ord.OrderName   
  6. from Customer cs right outer join tblorder ord   
  7. 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.
 
Join5.gif 
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
  1. select * from Customer  
  2. select * from tblOrder  
  3. select * from Customer cross join tblOrder  
  4. select * from Customer cross join tblOrder where Customer.CustId=tblOrder.CustId  
  5. select cs.CustomerName,cs.Amount,ord.OrderName   
  6. from Customer cs left outer join tblorder ord   
  7. on cs.CustId=ord.CustID  
  8.   
  9. select cs.CustomerName,cs.Amount,ord.OrderName   
  10. from Customer cs full outer join tblorder ord   
  11. on cs.CustId=ord.CustID  
  12.   
  13. select cs.CustomerName,cs.Amount,ord.OrderName   
  14. from Customer cs left outer join tblorder ord   
  15. on cs.CustId=ord.CustID  
  16. union   
  17. select cs.CustomerName,cs.Amount,ord.OrderName   
  18. from Customer cs right outer join tblorder ord   
  19. 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.


Similar Articles