Scribd
Upload
721 views8 pages

CS SQL Joins Class XII

The document discusses different types of SQL joins: 1. CARTESIAN JOIN/CROSS JOIN returns the Cartesian product of rows from two or more tables and combines all rows with all other rows. 2. EQUI JOIN combines tables based on matching column values and can join more than two tables. It is performed using '=' operator in the WHERE or ON clauses. 3. NATURAL JOIN automatically joins columns that have the same name and data type across tables and eliminates duplicate columns in the result. It does not use the ON clause.

Uploaded by

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

CS SQL Joins Class XII

The document discusses different types of SQL joins: 1. CARTESIAN JOIN/CROSS JOIN returns the Cartesian product of rows from two or more tables and combines all rows with all other rows. 2. EQUI JOIN combines tables based on matching column values and can join more than two tables. It is performed using '=' operator in the WHERE or ON clauses. 3. NATURAL JOIN automatically joins columns that have the same name and data type across tables and eliminates duplicate columns in the result. It does not use the ON clause.

Uploaded by

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

BML MUNJAL GREEN MEDOWS SCHOOL

COMPUTER SCIENCE CLASS XII


SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column
between them.

Let's look at a selection from the "Orders" table:

OrderID CustomerID OrderDate


10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20

Then, look at a selection from the "Customers" table:


CustomerID CustomerName ContactName Country
1 Alfreds Futterkiste Maria Anders Germany
2 Ana Trujillo Emparedados Ana Trujillo Mexico
helados
3 Antonio Moreno Taquería Antonio Mexico
Moreno

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the
"Customers" table. The relationship between the two tables above is the "CustomerID"
column.

CARTESIAN JOIN / CROSS JOIN


The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records
from two or more joined tables.

Syntax

The basic syntax of the CARTESIAN JOIN or the CROSS JOIN is as follows −
SELECT table1.column1, table2.column2...
FROM table1, table2 [, table3 ]
Example

Consider the following two tables.


Table 1 − CUSTOMERS table is as follows.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Table 2: ORDERS Table is as follows −
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables using CARTESIAN JOIN as follows −
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS, ORDERS;
This would produce the following result −
+----+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+---------------------+
| 1 | Ramesh | 3000 | 2009-10-08 00:00:00 |
| 1 | Ramesh | 1500 | 2009-10-08 00:00:00 |
| 1 | Ramesh | 1560 | 2009-11-20 00:00:00 |
| 1 | Ramesh | 2060 | 2008-05-20 00:00:00 |
| 2 | Khilan | 3000 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 2 | Khilan | 2060 | 2008-05-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 2060 | 2008-05-20 00:00:00 |
| 4 | Chaitali | 3000 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | 3000 | 2009-10-08 00:00:00 |
| 5 | Hardik | 1500 | 2009-10-08 00:00:00 |
| 5 | Hardik | 1560 | 2009-11-20 00:00:00 |
| 5 | Hardik | 2060 | 2008-05-20 00:00:00 |
| 6 | Komal | 3000 | 2009-10-08 00:00:00 |
| 6 | Komal | 1500 | 2009-10-08 00:00:00 |
| 6 | Komal | 1560 | 2009-11-20 00:00:00 |
| 6 | Komal | 2060 | 2008-05-20 00:00:00 |
| 7 | Muffy | 3000 | 2009-10-08 00:00:00 |
| 7 | Muffy | 1500 | 2009-10-08 00:00:00 |
| 7 | Muffy | 1560 | 2009-11-20 00:00:00 |
| 7 | Muffy | 2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+

EQUI JOIN
An EQUI JOIN is a type of join that combines tables based on matching values in specified
columns.

Please remember that:

 The column names do not need to be the same.


 The resultant table contains repeated columns.
 It is possible to perform an equi join on more than two tables.

 Syntax
 There are two ways to use equi join in SQL:

SELECT * FROM TABLENAME1, TABLENAME2

WHERE TABLENAME1.COLUMNNAME=TABLENAME2.COLUMNNAME;
OR

SELECT * FROM TABLENAME1 JOIN TABLENAME2


ON TABLENAME1.COLUMNNAME=TABLENAME2.COLUMNNAME;
 In the first method, after the SELECT keyword, the names of the columns that are to
be included in the result of the query are specified. The * operator is used if all the
columns need to be selected. After the FROM keyword, the tables which need to be
joined are specified. In the WHERE clause, the table and column names are specified
along with an = operator.

 In the second method, the JOIN keyword is used to join the tables based on the
condition provided after the ON keyword.

 Example
 The following tables have been created
 1. PRODUCT_LIST

ID Pname

1 Apples

2 Oranges

3 Mangoes

 2. PRODUCT_DETAILS

ID Brand Origin

1 Fresh Foods USA

2 Angro Ltd Pakistan

 3. BRAND_DETAILS

Brand OfficeAddress

Fresh Foods 123 Seattle USA

Angro Ltd 124 Lahore


SELECT * FROM PRODUCT_LIST JOIN PRODUCT_DETAILS
ON PRODUCT_LIST.ID=PRODUCT_DETAILS.ID;

SELECT PRODUCT_LIST.ID, PRODUCT_LIST.PNAME,


PRODUCT_DETAILS.BRAND, PRODUCT_DETAILS.ORIGIN,
BRAND_DETAILS.OFFICEADDRESS
FROM PRODUCT_LIST, PRODUCT_DETAILS, BRAND_DETAILS
WHERE PRODUCT_LIST.ID=PRODUCT_DETAILS.ID
AND PRODUCT_DETAILS.BRAND=BRAND_DETAILS.BRAND;

Another Example –
Let’s Consider the two tables given below.
Table name — Student
Select * from Student;

id name class city

3 Hina 3 Delhi

4 Megha 2 Delhi

6 Gouri 2 Delhi
Table name — Record
Select * from Record;

id class city

9 3 Delhi

10 2 Delhi

12 2 Delhi

1. EQUI JOIN :
Example –
SELECT student.name, student.id, record.class, record.city FROM student, record
WHERE student.city = record.city;
Or
SELECT student.name, student.id, record.class, record.city FROM student
JOIN record ON student.city = record.city;
Output :
name id class city

Hina 3 3 Delhi

Megha 4 3 Delhi

Gouri 6 3 Delhi

Hina 3 2 Delhi

Megha 4 2 Delhi

Gouri 6 2 Delhi

Hina 3 2 Delhi

Megha 4 2 Delhi

Gouri 6 2 Delhi

NON EQUI JOIN :


NON EQUI JOIN performs a JOIN using comparison operator other than equal(=) sign
like >, <, >=, <= with conditions.

Syntax:
SELECT *
FROM table_name1, table_name2
WHERE table_name1.column [> | < | >= | <= ] table_name2.column;

Example –
SELECT student.name, record.id, record.city FROM student, record
WHERE Student.id < Record.id ;
Output :
name id city

Hina 9 Delhi

Megha 9 Delhi

Gouri 9 Delhi

Hina 10 Delhi

Megha 10 Delhi

Gouri 10 Delhi

Hina 12 Delhi

Megha 12 Delhi

Gouri 12 Delhi

NATURAL JOIN
We have already learned that an EQUI JOIN performs a JOIN against equality or matching
column(s) values of the associated tables and an equal sign (=) is used as comparison
operator in the where clause to refer equality.
The NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns
with the same name of associated tables will appear once only.

- The associated tables have one or more pairs of identically named columns.
- The columns must be the same data type.
- Don’t use ON clause in a natural join.
Syntax:
SELECT * FROM TABLE1 NATURAL JOIN TABLE2;
Example:
Here is an example of SQL natural join between tow tables:
To get all the unique columns from foods and company tables, the following SQL statement
can be used:
SQL Code:
SELECT * FROM foods NATURAL JOIN company;

Points to remember:

o There is no need to specify the column names to join.


o The resultant table always contains unique columns.
o It is possible to perform a natural join on more than two tables.
o Do not use the ON clause.

You might also like