SQL Cross Joins

SQL Cartesian product :

 

You will be able to find Cartesian product with a Cartesian join. When we join every row of a table to every row of another table we get Cartesian join

SQL Cross Join:

 

Cross joins, where every row from one table is matched with every row from another.

Cartesian join and Cross join are one and the same thing.

If T1 and T2 are two sets then cross join = T1 X T2.

Examples of a cross join :

SELECT *

FROM emp CROSS JOIN dept

SELECT *

FROM emp, dept;

In the first example above it is explicitly written that it is a CROSS JOIN but in the second one it is implicit.

SQL Equi-join 

 

The join condition determines whether the join is an equi-join or a non equi-join. when we relate two tables

on a join condition by equating the columns from the tables, it is an equi-join. when we relate two tables on a join condition by an operator other than equality it is an non-equi-join. A query may contain equi-joins as

well as non-equi-joins.

Examples of Equi-join :

SELECT emp. deptno, bonus.comm

FROM emp bonus

WHERE emp.ename = bonus.ename

SELECT * FROM emp

INNER JOIN dept

ON emp.DeptID = dept.DeptID

An Example of INNER JOIN

The "PERSONS" table:

P_Id	LastName	First Name	Address	City
1	Hansen	Timo	teivn10	Sandnes
2	Svendson	Tove	Borgvn23	Sandnes
3	Pettersen	Kari	Storgt20	Stavanger

The "ORDERS" table:

O_Id	Order No	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

 Now we want to list all the persons with any orders.

 SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

 FROM Persons

 INNER JOIN Orders

 ON Persons.P_Id=Orders.P_Id

 ORDER BY Persons.LastName

 The result-set will look like this :

 

LastName	First Name	Order No
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

An Example of LEFT OUTER JOIN

 XXTable :

XX	ID
xx1	1
xx2	2
xx3	3
xx4	4
xx5	5
xx7	7

 YY Table :

ID	YY
1	yy1
2	yy2
3	yy3
4	yy4
6	yy6
7	yy7

SELECT xx, xxtable.id, yytable.id, yy

FROM xxtable

LEFT OUTER JOIN yytable

ON xxtable.id=yytable.id;

XX	XXtable.id	YYtable.id	YY
xx1	1	1	yy1
xx2	2	2	yy2
xx3	3	3	yy3
xx4	4	4	yy4
xx5	5	-
xx7	7	7	yy7

An Example of RIGHT OUTER JOIN (Taking same Xxtable & YYTable tables as above)

Now, let's say we wanted all of the rows in the table 2 (yy table, the "right" table) in our result, regardless of whether they matched with rows in table 1 (the xx table, the "left" table), we could perform a RIGHT OUTER JOIN between tables xxtable and yytable.

The following SQL syntax :

SELECT xx, xxtable.id, yytable.id, yy

FROM xxtable

RIGHT OUTER JOIN yytable

ON xxtable.id=yytable.id;

Would give the following result :

XX	XXtable.id	YYtable.id	YY
xx1	1	1	yy1
xx2	2	2	yy2
xx3	3	3	yy3
xx4	4	4	yy4
-	-	6	yy6
xx7	7	7	yy7

In this example, there is a NULL value for the fields xxtable.id and xx where yytable.id=6 since xxtable does not have a record with xxtable.id=6. Note that all of the records of the right table in a RIGHT OUTER JOIN get displayed, regardless of whether or not they match rows in the left table.

An Example of FULL JOIN

 Table P

P_id	Last Name	First Name	City
1	Dhall	Sachin	Delhi
2	Gupta	Pankaj	Bangalore
3	Kumar	Sanjeev	Chandigarh

 Table O

O_id	Order No	p_id
1	111	3
2	222	3
3	333	14
4	444	2
5	555	2

SELECT P.LastName, P.FirstName, O.OrderNo

FROM P

FULL JOIN O

ON P.P_Id=O.P_Id

ORDER BY P.LastName

LastName	First Name	Order No
NULL	NULL	333
Dhall	Sachin	NULL
Gupta	Pankaj	444
Gupta	Pankaj	555
Kumar	Sanjeev	111
Kumar	Sanjeev	222

Explicit vs. Implicit SQL Joins

 

The explicit join is easier to read and the implicit syntax is difficult to understand and more prone to errors. Moreover implicit syntax is now a day’s outdated.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation": 
 The "explicit join notation" uses the JOIN keyword to specify the table to join, and the
ON keyword to specify the predicates for the join, as in the following example:

SELECT * FROM employee INNER JOIN department

ON employee.DepartmentID = department.DepartmentID;

The "implicit join notation" simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates (which function comparably to the join-predicates in the explicit notation).

The following example shows a query which is equivalent to the one from the previous example, but this time written using the implicit join notation :

SELECT * 

FROM   employee, department

WHERE employee.DepartmentID = department.DepartmentID;