Friday, May 28, 2010

Understanding the CROSS JOIN.

A cross join, also known as Cartesian product, between two table joins each row from one table with each row of the other table. The number of rows in the result set is the number of  rows in the first table multiplied by the number of rows in the second table. This implies that if Table A has 10 rows and Table B has 5 Rows, then all 10 rows of Table A are joined with all 5 rows of Table B. therefore, the result set will contain 50 rows.
                     Other words, a cross join; Cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the Cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or join-condition is absent in statement.
 

Cross join Syntax is below.
Select[ column list]
From [left joined table]   
CROSS JOIN [right joined table]

Query of CROSS JOIN.

CREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,

 firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales
(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);

command in CROSS JOIN

SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
CROSS JOIN sales AS s

The result is displayed below with CROSS JOIN the both table customer and sales.

1 comment:

vikash said...

it's very helpful for me becuz it give currect information.

Recent Posts