SQL-Server-Cross-Join .

In this session i have discussed the SQL Server Cross-Join .

The cross join also known as cartesian product If we do not use where clause in cross join statement . In the cross join first row of first table multiply with every row of second table and second row of first table multiply with every row of second table and so on .....

Supossed that if first table has 2 rows and second table has 2 rows than the number of row after mulitply is 4 (2*2).


Cross Join syntax

    SELECT column_names  FROM tbl1
    CROSS JOIN tbl2
    
    


Database Demo

The following data shown by Computer_Details table of abc database .

Computer_Code Computer_Details Computer_Price
1 Core I3 Processor , 2 GB Ram , 250 GB Hard Disk . 35000
2 Core I5 Processor , 8 GB Ram , 500 GB Hard Disk . 45000

The below is Accessories table of abc database .

Accessories_Code Accessories_Details Accessories_Price
1 Speaker 200
2 Mouse 100

Cross Join Example .

The below sql cross join code display the 4 rows data . The first table two rows multiply with second table two rows and produce the 4 rows data as follows .

SELECT Computer.Computer_Details , Accessories.Accessories_Details
Computer.Computer_Price , Accessories.Accessories_Price , 
Computer.Computer_Price+Accessories.Accessories_Price
as Total FROM Computer
CROSS JOIN Accessories
    

The output is look like below .

Computer_Details Accessories_Details Computer_Price Accessories_Price Total
Core I3 Processor , 2 GB Ram , 250 GB Hard Disk . Speaker 35000 200 35200
Core I3 Processor , 2 GB Ram , 250 GB Hard Disk . Mouse 35000 100 35100
Core I5 Processor , 8 GB Ram , 500 GB Hard Disk . Speaker 45000 200 45200
Core I5 Processor , 8 GB Ram , 500 GB Hard Disk . Speaker 45000 100 45100

Welcome to AndroidATPoint.

back image                     next image