1Keydata.com

SQL Cross Join


  SQL > SQL Commands > Cross Join

A cross join (also called a Cartesian join) is a join of tables without specifying the join condition. In this scenario, the query would return all possible combination of the tables in the SQL query. To see this in action, let's use the following example:

Table Store_Information
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

Table Geography
region_name store_name
East Boston
East New York
West Los Angeles
West San Diego

The following SQL statement is a Cartesian join between the Store_Information and the Geography tables:

SELECT A1.store_name STORE1, A2.store_name STORE2, A2.Sales SALES
FROM Geography A1
JOIN Store_Information A2

Result:

STORE1   STORE2   SALES
Boston   Los Angeles   $1500
New York   Los Angeles   $1500
Los Angeles   Los Angeles   $1500
San Diego   Los Angeles   $1500
Boston   San Diego   $250
New York   San Diego   $250
Los Angeles   San Diego   $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.store_name STORE1, A2.store_name STORE2, A2.Sales SALES
FROM Geography A1, Store_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.

SQL SELECT UNIQUE >>

Link to this page: If you find this page useful, we encourage you to link to this page. Simply copy and paste the code below to your website, blog, or profile.


More 1Keydata Tutorials
Data Warehousing:   Data Modeling   Business Intelligence   Data Integrity   Star Schema   Logical Data Model
CSS Tutorial:   CSS Background   CSS Border   CSS Padding   CSS Overflow   CSS Margin   CSS Cursor
PHP Tutorial:   PHP Foreach   PHP Redirect   PHP Array   PHP Include   PHP Explode   PHP For Loop   PHP Substr
HTML Tutorial:   Radio Button   HTML Checkbox   HTML Drop Down   Basic HTML Tags   Text Input   Color Codes


Copyright © 2012   All Rights Reserved.     Privacy Policy  



SQL SELECT
SQL DISTINCT
SQL WHERE
SQL AND OR
SQL IN
SQL BETWEEN
SQL Wildcard
SQL LIKE
SQL ORDER BY
SQL Functions
SQL Average
SQL COUNT
SQL MAX
SQL MIN
SQL SUM
SQL GROUP BY
SQL HAVING
SQL ALIAS
SQL AS
SQL JOIN
SQL INNER JOIN
SQL OUTER JOIN
SQL LEFT OUTER JOIN
SQL CROSS JOIN
SQL SELECT UNIQUE
SQL ROUND
SQL CAST
SQL CONVERT

SQL CREATE TABLE
SQL CONSTRAINT
SQL NOT NULL
SQL DEFAULT
SQL UNIQUE
SQL CHECK
SQL PRIMARY KEY
SQL FOREIGN KEY
SQL View
SQL CREATE VIEW
SQL Index
SQL CREATE INDEX
SQL ALTER TABLE
SQL DROP TABLE
SQL TRUNCATE TABLE
SQL USE
SQL CREATE DATABASE
SQL DROP DATABASE
SQL INSERT INTO
SQL UPDATE
SQL DELETE FROM

SQL Jobs

Site Map
Resources