KEMBAR78
Cross Join | PDF
Scribd
Upload
214 views2 pages

Cross Join

Cross join produces the Cartesian product of the tables involved in the join, returning all possible combinations of rows from the tables. It does not use a join condition, unlike other types of joins. The document provides the syntax for a cross join using the CROSS JOIN keyword or a comma between the table names. It also creates two sample tables, EMP and DEPT, and provides examples of cross join queries on those tables with and without a WHERE clause and using ORDER BY.

Uploaded by

Venu D
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
214 views2 pages

Cross Join

Cross join produces the Cartesian product of the tables involved in the join, returning all possible combinations of rows from the tables. It does not use a join condition, unlike other types of joins. The document provides the syntax for a cross join using the CROSS JOIN keyword or a comma between the table names. It also creates two sample tables, EMP and DEPT, and provides examples of cross join queries on those tables with and without a WHERE clause and using ORDER BY.

Uploaded by

Venu D
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

Definition

Cross Join produces Cartesian product of the tables


which are participating in the Join queries
that�s why it�s also known by the name of Cartesian Join.

Generally we use CROSS JOIN


when there is no relationship between
participating tables.

Syntax

SELECT column names FROM table1 CROSS JOIN table 2 ;

Or
SELECT column names FROM table1, table 2 ;

Also with cross join we do not have any ON or USING join condition.

Table 1: EMP this is a very simple table


which consists of 3 columns
emp id, emp name and emp salary
where column emp id is the primary key.

The query to create this table is

CREATE TABLE emp


(
emp_id NUMBER(2) CONSTRAINT emp_col1_pk PRIMARY KEY,
emp_name VARCHAR2(20),
emp_salary NUMBER(5)
);

Table 2: The second table is DEPT this table also consists of 3 columns
dept id, dept name and emp id.
Column dept id is the primary key here and column emp id is
the foreign key reference from the emp table.

The query to create this table is

CREATE TABLE dept


(dept_id NUMBER(2) CONSTRAINT emp_col1_pk
PRIMARY KEY,dept_name VARCHAR2(20),emp_id CONSTRAINT
dept_col3_fk REFERENCES emp9(emp_id)
);

Query 1: CROSS JOIN

SELECT emp_name, dept_name FROM emp CROSS JOIN dept;


Since this cross join produces a Cartesian product therefore
the total number of rows in the result will be equal to total number
of rows in table 1 multiplied by total number of rows in table 2.
Query 2: CROSS JOIN with WHERE clause.

SELECT emp_name,dept_name FROM emp


CROSS JOIN dept WHERE dept_name = 'IT';
Query 3 : Cross Join with Order By clause

SELECT emp_name,dept_name FROM emp CROSS JOIN


dept WHERE dept_name = 'IT' ORDER BY emp_name;

SELECT emp_name,dept_name FROM emp CROSS JOIN dept


WHERE dept_name = 'IT' ORDER BY emp_name DESC;

You might also like