Scribd
Upload
5 views20 pages

Unit 3 - Join - LAB

The document provides SQL examples for creating and manipulating two tables: CUSTOMERS and ORDERS. It demonstrates various types of joins including INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, and FULL JOIN, along with their respective outputs. Additionally, it includes examples of using WHERE clauses with these joins to filter results based on specific conditions.

Uploaded by

bijaybhattarai2004
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
5 views20 pages

Unit 3 - Join - LAB

The document provides SQL examples for creating and manipulating two tables: CUSTOMERS and ORDERS. It demonstrates various types of joins including INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, and FULL JOIN, along with their respective outputs. Additionally, it includes examples of using WHERE clauses with these joins to filter results based on specific conditions.

Uploaded by

bijaybhattarai2004
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

Join Examples:

CREATE TABLE CUSTOMERS (


ID INT NOT NULL,
NAME VARCHAR(15) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR(25),
SALARY DECIMAL(10, 2),
PRIMARY KEY(ID)
);
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', '32', 'Dharan', 2000),
(2, 'Khilan', '25', 'Kathmandu', 1500),
(3, 'Kaushik', '23', 'Biratnagar', 2500),
(4, 'Chaitali', '26', 'Pokhara', 6500),
(5, 'Hardik','27', 'Birganj', 8500),
(6, 'Komal', '22', 'Hetauda', 9000),
(7, 'Muffy', '24', 'Butwal', 5500);
ORDERS Table –

CREATE TABLE ORDERS (


OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2)
);
INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);
Joining Tables –

SELECT [Link], [Link], [Link], [Link]


FROM CUSTOMERS a, ORDERS b
WHERE [Link] = b.CUSTOMER_ID;

Output
The table is displayed as follows −

ID NAME DATE AMOUNT

3 Kaushik 2009-10-08 00:00:00 3000.00

3 Kaushik 2009-10-08 00:00:00 1500.00

2 Khilan 2009-11-20 00:00:00 1560.00

4 Chaitali 2008-05-20 00:00:00 2060.00

Inner Join Query −

Let us now combine these two tables using the Inner Join query as shown below
−

SELECT ID, NAME, AMOUNT, DATE


FROM CUSTOMERS
INNER JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;
Output
The table is displayed as follows −

ID NAME DATE AMOUNT

3 Kaushik 2009-10-08 00:00:00 3000.00

3 Kaushik 2009-10-08 00:00:00 1500.00

2 Khilan 2009-11-20 00:00:00 1560.00

4 Chaitali 2008-05-20 00:00:00 2060.00

Inner Join with WHERE Clause

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS


INNER JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID
WHERE [Link] > 2000.00;

Output
The table is displayed as follows −

ID NAME DATE AMOUNT

3 Kaushik 2009-10-08 00:00:00 3000.00

4 Chaitali 2008-05-20 00:00:00 2060.00

MySQL Left Join

SELECT ID, NAME, AMOUNT, DATE


FROM CUSTOMERS
LEFT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

Output
The joined result-set is obtained as −

ID NAME AMOUNT DATE

1 Ramesh NULL NULL

2 Khilan 1560.00 2009-11-20 00:00:00

3 Kaushik 1500.00 2009-10-08 00:00:00

3 Kaushik 3000.00 2009-10-08 00:00:00

4 Chaitali 2060.00 2008-05-20 00:00:00

5 Hardik NULL NULL

6 Komal NULL NULL

7 Muffy NULL NULL

Left Join with WHERE Clause

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS


LEFT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID
WHERE [Link] > 2000.00;

Output
The output is obtained as − −
ID NAME DATE AMOUNT

2009-10-08
3 Kaushik 3000.00
00:00:00

2008-05-20
4 Chaitali 2060.00
00:00:00

MySQL Right Join

SELECT ID, NAME, AMOUNT, DATE


FROM CUSTOMERS
RIGHT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

Output
This would produce the following result −

ID NAME AMOUNT DATE

3 Kaushik 3000.00 2009-10-08 00:00:00

3 Kaushik 1500.00 2009-10-08 00:00:00

2 Khilan 1560.00 2009-11-20 00:00:00

4 Chaitali 2060.00 2008-05-20 00:00:00

Right Join with WHERE Clause

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS


RIGHT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID
WHERE [Link] > 1000.00;

Output
The resultant table is obtained as follows −

ID NAME DATE Amount

3 Kaushik 2009-10-08 00:00:00 3000.00

3 Kaushik 2009-10-08 00:00:00 1500.00

2 Khilan 2009-11-20 00:00:00 1560.00

4 Chaitali 2008-05-20 00:00:00 2060.00

MySQL Cross Join

A MySQL Cross Join combines each row of the first table with each row of
second table. It is a basic type of inner join that is used to retrieve the
Cartesian product (or cross product) of two individual tables (i.e. permutations).

SELECT ID, NAME, AMOUNT, DATE


FROM CUSTOMERS
CROSS JOIN ORDERS;

MySQL - Full Join


MySQL Full Join creates a new table by joining two tables as a whole. The
joined table contains all records from both the tables and fill in NULLs for
missing matches on either side. In short, full join is a type of outer join that
combines the results of both left and right joins.

SELECT ID, NAME, AMOUNT, DATE


FROM CUSTOMERS
LEFT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID;

Output
The resultant table is produced as follows −

ID NAME AMOUNT DATE

1 Ramesh NULL NULL

2 Khilan 1560 2009-11-20 00:00:00

3 Kaushik 3000 2009-10-08 00:00:00

3 Kaushik 1500 2009-10-08 00:00:00

4 Chaitali 2060 2008-05-20 00:00:00

5 Hardik NULL NULL

6 Komal NULL NULL

7 Muffy NULL NULL

Full Join with WHERE Clause


SELECT ID, NAME, DATE, AMOUNT
FROM CUSTOMERS
LEFT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID
WHERE [Link] > 2000.00
UNION

SELECT ID, NAME, DATE, AMOUNT


FROM CUSTOMERS
RIGHT JOIN ORDERS
ON [Link] = ORDERS.CUSTOMER_ID
WHERE [Link] > 2000.00;

Output
The resultant table after applying the where clause with full join contains the
rows that has amount values greater than 2000.00 −

ID NAME DATE AMOUNT

3 Kaushik 2009-10-08 00:00:00 3000.00

4 Chaitali 2008-05-20 00:00:00 2060.00

……………………..
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| assertions |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.22 sec)

mysql> create database company;


Query OK, 1 row affected (0.11 sec)

mysql> use company;


Database changed
mysql> CREATE TABLE CUSTOMERS (
-> ID INT NOT NULL,
-> NAME VARCHAR(15) NOT NULL,
-> AGE INT NOT NULL,
-> ADDRESS VARCHAR(25),
-> SALARY DECIMAL(10, 2),
-> PRIMARY KEY(ID)
-> );
Query OK, 0 rows affected (0.19 sec)

mysql> desc customers;


+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID | int | NO | PRI | NULL | |
| NAME | varchar(15) | NO | | NULL | |
| AGE | int | NO | | NULL | |
| ADDRESS | varchar(25) | YES | | NULL | |
| SALARY | decimal(10,2) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.07 sec)

mysql> INSERT INTO CUSTOMERS VALUES


-> (1, 'Ramesh', '32', 'Ahmedabad', 2000),
-> (2, 'Khilan', '25', 'Delhi', 1500),
-> (3, 'Kaushik', '23', 'Kota', 2500),
-> (4, 'Chaitali', '26', 'Mumbai', 6500),
-> (5, 'Hardik','27', 'Bhopal', 8500),
-> (6, 'Komal', '22', 'Hyderabad', 9000),
-> (7, 'Kamal', '22', 'Kanpur', 10000);
Query OK, 7 rows affected (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> select * from customers;


+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | Kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 26 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Hyderabad | 9000.00 |
| 7 | Kamal | 22 | Kanpur | 10000.00 |
+----+----------+-----+-----------+----------+
7 rows in set (0.01 sec)

mysql> CREATE TABLE ORDERS (


-> OID INT NOT NULL,
-> DATE VARCHAR (20) NOT NULL,
-> CUSTOMER_ID INT NOT NULL,
-> AMOUNT DECIMAL (18, 2)
-> );
Query OK, 0 rows affected (0.09 sec)

mysql> desc orders;


+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| OID | int | NO | | NULL | |
| DATE | varchar(20) | NO | | NULL | |
| CUSTOMER_ID | int | NO | | NULL | |
| AMOUNT | decimal(18,2) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> INSERT INTO ORDERS VALUES


-> (102, '2009-10-08 00:00:00', 3, 3000.00),
-> (100, '2009-10-08 00:00:00', 3, 1500.00),
-> (101, '2009-11-20 00:00:00', 2, 1560.00),
-> (103, '2008-05-20 00:00:00', 4, 2060.00);
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from orders;


+-----+---------------------+-------------+---------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+---------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000.00 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500.00 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560.00 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060.00 |
+-----+---------------------+-------------+---------+
4 rows in set (0.00 sec)
mysql> SELECT [Link], [Link], [Link], [Link]
-> FROM CUSTOMERS a, ORDERS b
-> WHERE [Link] = b.CUSTOMER_ID;
+----+----------+---------------------+---------+
| ID | NAME | DATE | AMOUNT |
+----+----------+---------------------+---------+
| 3 | Kaushik | 2009-10-08 00:00:00 | 3000.00 |
| 3 | Kaushik | 2009-10-08 00:00:00 | 1500.00 |
| 2 | Khilan | 2009-11-20 00:00:00 | 1560.00 |
| 4 | Chaitali | 2008-05-20 00:00:00 | 2060.00 |
+----+----------+---------------------+---------+
4 rows in set (0.00 sec)

mysql> # Inner Join Query −


mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> INNER JOIN ORDERS
-> ON [Link] = ORDERS.CUSTOMER_ID;
+----+----------+---------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+---------+---------------------+
| 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 |
| 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060.00 | 2008-05-20 00:00:00 |
+----+----------+---------+---------------------+
4 rows in set (0.00 sec)

mysql> # Inner Join with WHERE Clause


mysql> SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
-> INNER JOIN ORDERS
-> ON [Link] = ORDERS.CUSTOMER_ID
-> WHERE [Link] > 2000.00;
+----+----------+---------------------+---------+
| ID | NAME | DATE | AMOUNT |
+----+----------+---------------------+---------+
| 3 | Kaushik | 2009-10-08 00:00:00 | 3000.00 |
| 4 | Chaitali | 2008-05-20 00:00:00 | 2060.00 |
+----+----------+---------------------+---------+
2 rows in set (0.03 sec)

mysql> # Left Join Query


mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> LEFT JOIN ORDERS
-> ON [Link] = ORDERS.CUSTOMER_ID;
+----+----------+---------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+---------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 |
| 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 |
| 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060.00 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Kamal | NULL | NULL |
+----+----------+---------+---------------------+
8 rows in set (0.00 sec)

mysql> # Left Join with WHERE Clause


mysql> SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
-> LEFT JOIN ORDERS
-> ON [Link] = ORDERS.CUSTOMER_ID
-> WHERE [Link] > 2000.00;
+----+----------+---------------------+---------+
| ID | NAME | DATE | AMOUNT |
+----+----------+---------------------+---------+
| 3 | Kaushik | 2009-10-08 00:00:00 | 3000.00 |
| 4 | Chaitali | 2008-05-20 00:00:00 | 2060.00 |
+----+----------+---------------------+---------+
2 rows in set (0.00 sec)

mysql> # Right join Query


mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> RIGHT JOIN ORDERS
-> ON [Link] = ORDERS.CUSTOMER_ID;
+------+----------+---------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+---------+---------------------+
| 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 |
| 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060.00 | 2008-05-20 00:00:00 |
+------+----------+---------+---------------------+
4 rows in set (0.00 sec)

mysql> # Right Join with WHERE Clause


mysql> SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
-> RIGHT JOIN ORDERS
-> ON [Link] = ORDERS.CUSTOMER_ID
-> WHERE [Link] > 1000.00;
+------+----------+---------------------+---------+
| ID | NAME | DATE | AMOUNT |
+------+----------+---------------------+---------+
| 3 | Kaushik | 2009-10-08 00:00:00 | 3000.00 |
| 3 | Kaushik | 2009-10-08 00:00:00 | 1500.00 |
| 2 | Khilan | 2009-11-20 00:00:00 | 1560.00 |
| 4 | Chaitali | 2008-05-20 00:00:00 | 2060.00 |
+------+----------+---------------------+---------+
4 rows in set (0.00 sec)

mysql> # Cross Join query


mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> CROSS JOIN ORDERS;
+----+----------+---------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+---------+---------------------+
| 1 | Ramesh | 2060.00 | 2008-05-20 00:00:00 |
| 1 | Ramesh | 1560.00 | 2009-11-20 00:00:00 |
| 1 | Ramesh | 1500.00 | 2009-10-08 00:00:00 |
| 1 | Ramesh | 3000.00 | 2009-10-08 00:00:00 |
| 2 | Khilan | 2060.00 | 2008-05-20 00:00:00 |
| 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 |
| 2 | Khilan | 1500.00 | 2009-10-08 00:00:00 |
| 2 | Khilan | 3000.00 | 2009-10-08 00:00:00 |
| 3 | Kaushik | 2060.00 | 2008-05-20 00:00:00 |
| 3 | Kaushik | 1560.00 | 2009-11-20 00:00:00 |
| 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 |
| 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060.00 | 2008-05-20 00:00:00 |
| 4 | Chaitali | 1560.00 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 1500.00 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 3000.00 | 2009-10-08 00:00:00 |
| 5 | Hardik | 2060.00 | 2008-05-20 00:00:00 |
| 5 | Hardik | 1560.00 | 2009-11-20 00:00:00 |
| 5 | Hardik | 1500.00 | 2009-10-08 00:00:00 |
| 5 | Hardik | 3000.00 | 2009-10-08 00:00:00 |
| 6 | Komal | 2060.00 | 2008-05-20 00:00:00 |
| 6 | Komal | 1560.00 | 2009-11-20 00:00:00 |
| 6 | Komal | 1500.00 | 2009-10-08 00:00:00 |
| 6 | Komal | 3000.00 | 2009-10-08 00:00:00 |
| 7 | Kamal | 2060.00 | 2008-05-20 00:00:00 |
| 7 | Kamal | 1560.00 | 2009-11-20 00:00:00 |
| 7 | Kamal | 1500.00 | 2009-10-08 00:00:00 |
| 7 | Kamal | 3000.00 | 2009-10-08 00:00:00 |
+----+----------+---------+---------------------+
28 rows in set (0.00 sec)

mysql> # Full Join Query-produce the union of two tables


mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> LEFT JOIN ORDERS
-> ON [Link] = ORDERS.CUSTOMER_ID
-> UNION
-> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> RIGHT JOIN ORDERS
-> ON [Link] = ORDERS.CUSTOMER_ID;
+------+----------+---------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+---------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 |
| 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 |
| 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060.00 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Kamal | NULL | NULL |
+------+----------+---------+---------------------+
8 rows in set (0.05 sec)

mysql> # Full Join with WHERE Clause


mysql> SELECT ID, NAME, DATE, AMOUNT
-> FROM CUSTOMERS
-> LEFT JOIN ORDERS
-> ON [Link] = ORDERS.CUSTOMER_ID
-> WHERE [Link] > 2000.00
->
-> UNION
->
-> SELECT ID, NAME, DATE, AMOUNT
-> FROM CUSTOMERS
-> RIGHT JOIN ORDERS
-> ON [Link] = ORDERS.CUSTOMER_ID
-> WHERE [Link] > 2000.00;
+------+----------+---------------------+---------+
| ID | NAME | DATE | AMOUNT |
+------+----------+---------------------+---------+
| 3 | Kaushik | 2009-10-08 00:00:00 | 3000.00 |
| 4 | Chaitali | 2008-05-20 00:00:00 | 2060.00 |
+------+----------+---------------------+---------+
2 rows in set (0.00 sec)

mysql>

You might also like