Cross Join | cross join SQL | Join - sql - sql tutorial - learn sql




 cross join
  • A cross join (also called a Cartesian join) is a join of tables without specifying the join condition,the query would return all possible combination of the tables in the SQL query.
  • The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.
  • If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.
  • An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

Example

Table Product_Information

Product_Name price Txn_Date
Los Angeles 1500 Jan-05-2017
San Diego 250 Jan-07-2017
Los Angeles 300 Jan-08-2017
Boston 700 Jan-08-2017

Table Characteristics

Region_Name Product_Name
East Boston
East New York
West Los Angeles
West San Diego
  • The following SQL statement is a Cartesian join between
the Product_Information and the Characteristics tables:
SELECT A1.Product_Name PRODUCT1, A2. Product_Name PRODUCT2,A2.Price PRICES 
FROM Characteristics A1 
JOIN Product_Information A2;

Result:

Product1 Product2 price
Boston Los Angeles 1500
New York Los Angeles 1500
Los Angeles Los Angeles 1500
San Diego Los Angeles 1500
Boston New York 250
New York Los Angeles 250
Los Angeles Los Angeles 250
San Diego San Diego 250
Boston Los Angeles 300
New York Los Angeles 300
Los Angeles Los Angeles 300
San Diego Los Angeles 300
Boston Boston 700
New York Boston 700
Los Angeles Boston 700
San Diego Boston 700
  • An alternative way of specifying a cross join is
SELECT A1.Product_name PRODUCT1, A2. Product_name PRODUCT2, A2.Price PRICES 
FROM Characteristics A1, Product_Information A2;
  • Ø A cross join is seldom the desired result. Rather, it is an indication that some required join condition is missing in the SQL query.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

CROSS Join Example 1

CROSS Join Example
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

CROSS Join Example 2

CROSS Join Example 2
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

All SQL Joins

All SQL Joins

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Cross Join in SQL