It will return Cartesian Product of sets of rows from the 2 joined table. Here each row of the first table is combined with each row from the second table. Total number of rows present in the final output will be the product of number of rows present in both the tables.
For example PRODUCTS Table has:
product_id | product_name | supplier_id | category_id | quantity_per_unit | price |
---|---|---|---|---|---|
1 | Aniseed Syrup | 1 | 2 | 12 – 550 ml bottles | 10 |
2 | Cajun Seasoning | 2 | 2 | 48 – 6 oz jars | 22 |
3 | Green Tea | 1 | 1 | 10 boxes x 20 bags | 18 |
4 | Gumbo Mix | 2 | 2 | 36 boxes | 21.35 |
5 | Grandmas Boysenberry Spread | 3 | 2 | 12 – 8 oz jars | 25 |
6 | Uncle Bobs Organic Dried Pears | 3 | 7 | 12 – 1 lb pkgs. | 30 |
7 | Northwoods Cranberry Sauce | 3 | 2 | 12 – 12 oz jars | 40 |
8 | Mishi Kobe Niku | 4 | 6 | 18 – 500 g pkgs. | 97 |
SUPPLIERS Table has:
supplier_id | supplier_name | contact_name | address | city | postal_code | country | phone |
---|---|---|---|---|---|---|---|
1 | Exotic Liquids | Charlotte Cooper | 49 Gilbert St. | London | EC1 4SD | UK | (171) 555-2222 |
2 | New Orleans Cajun Delights | Shelley Burke | P.O. Box 78934 | New Orleans | 70117 | USA | (100) 555-4822 |
3 | Grandma Kellys Homestead | Regina Murphy | 707 Oxford Rd. | Ann Arbor | 48104 | USA | (313) 555-5735 |
4 | Tokyo Traders | Yoshi Nagase | 9-8 Sekimai Musashino-shi | Tokyo | 100 | Japan | (03) 3555-5011 |
5 | Mayumis | Mayumi Ohno | 92 Setsuko Chuo-ku | Osaka | 545 | Japan | (06) 431-7877 |
6 | Pavlova, Ltd. | Ian Devling | 74 Rose St. Moonie Ponds | Melbourne | 3058 | Australia | (03) 444-2343 |
Lets execute this cross join query:
SELECT * FROM PRODUCTS CROSS JOIN SUPPLIERS
Its output will be like:
product_id | product_name | supplier_id | category_id | quantity_per_unit | price | supplier_name | contact_name | address | city | postal_code | country | phone |
---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Aniseed Syrup | 1 | 2 | 12 – 550 ml bottles | 10 | Exotic Liquids | Charlotte Cooper | 49 Gilbert St. | London | EC1 4SD | UK | (171) 555-2222 |
1 | Aniseed Syrup | 2 | 2 | 12 – 550 ml bottles | 10 | New Orleans Cajun Delights | Shelley Burke | P.O. Box 78934 | New Orleans | 70117 | USA | (100) 555-4822 |
1 | Aniseed Syrup | 3 | 2 | 12 – 550 ml bottles | 10 | Grandma Kellys Homestead | Regina Murphy | 707 Oxford Rd. | Ann Arbor | 48104 | USA | (313) 555-5735 |
1 | Aniseed Syrup | 4 | 2 | 12 – 550 ml bottles | 10 | Tokyo Traders | Yoshi Nagase | 9-8 Sekimai Musashino-shi | Tokyo | 100 | Japan | (03) 3555-5011 |
1 | Aniseed Syrup | 5 | 2 | 12 – 550 ml bottles | 10 | Mayumis | Mayumi Ohno | 92 Setsuko Chuo-ku | Osaka | 545 | Japan | (06) 431-7877 |
1 | Aniseed Syrup | 6 | 2 | 12 – 550 ml bottles | 10 | Pavlova, Ltd. | Ian Devling | 74 Rose St. Moonie Ponds | Melbourne | 3058 | Australia | (03) 444-2343 |
1 | Aniseed Syrup | 7 | 2 | 12 – 550 ml bottles | 10 | Specialty Biscuits, Ltd. | Peter Wilson | 29 Kings Way | Manchester | M14 GSD | UK | (161) 555-4448 |
1 | Aniseed Syrup | 8 | 2 | 12 – 550 ml bottles | 10 | PB Knckebrd AB | Lars Peterson | Kaloadagatan 13 | Gteborg | S-345 67 | Sweden | 031-987 65 43 |
1 | Aniseed Syrup | 9 | 2 | 12 – 550 ml bottles | 10 | Refrescos Americanas LTDA | Carlos Diaz | Av. das Americanas 12.890 | So Paulo | 5442 | Brazil | (11) 555 4640 |
Learn more : SQL Tutorial , SQL Interview Questions