SQL JOINS
JOINS : join is used to combine columns from one (self-join) or more tables based on the values of the common columns between the tables. A JOIN is a means for combining fields from two tables by using values common to each.There are four types of joins :-
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
sample tables :Suppose we have two tables called basket_a and basket_b that stores fruits:
|
CREATE TABLE basket_a (id INT PRIMARY KEY, fruit VARCHAR (100) NOT NUL);
INSERT INTO basket_a (id, fruit)VALUES(1, 'Apple'),
INSERT INTO basket_a (id, fruit)VALUES (2, 'Orange'),
INSERT INTO basket_a (id, fruit)VALUES(3, 'Banana'),
INSERT INTO basket_a (id, fruit)VALUES (4, 'Cucumber');
|
|
CREATE TABLE basket_b (id INT PRIMARY KEY,fruit VARCHAR (100) NOT NUL);
INSERT INTO basket_b (id, fruit)VALUES(1, 'Orange'),
INSERT INTO basket_b (id, fruit)VALUES(2, 'Apple'),
INSERT INTO basket_b (id, fruit)VALUES(3, 'Watermelon'),
INSERT INTO basket_b (id, fruit)VALUES(4, 'Pear');
|
INNER JOIN : This type of join returns those records which have matching values in both tables.
| SELECT a.id id_a, a.fruit fruit_a, b.id id_b, b.fruit fruit_b FROM basket_a a INNER JOIN basket_b b ON a.fruit = b.fruit; |
Output :
| id_a |
fruit_a |
id_b |
fruit_b |
| 1 |
apple |
2 |
apple |
| 2 |
orange |
1 |
orange |
LEFT JOIN : returns all rows from the left table, even if there are no matches in the right table.
| SELECT a.id id_a, a.fruit fruit_a, b.id id_b, b.fruit fruit_b FROM basket_a a LEFT JOIN basket_b b ON a.fruit = b.fruit; |
Output :
| id_a |
fruit_a |
id_b |
fruit_b |
| 1 |
apple |
2 |
apple |
| 2 |
orange |
1 |
orange |
| 3 |
banana |
null |
null |
| 4 |
cucumber |
null |
null |
RIGHT JOIN : returns all rows from the right table, even if there are no matches in the left table.
| SELECT a.id id_a, a.fruit fruit_a, b.id id_b, b.fruit fruit_b FROM basket_a a RIGHT JOIN basket_b b ON a.fruit = b.fruit; |
Output :
| id_a |
fruit_a |
id_b |
fruit_b |
| 2 |
orange |
1 |
orange |
| 1 |
apple |
2 |
apple |
| null |
null |
3 |
watermelon |
| null |
null |
4 |
pear |
FULL JOIN: returns rows when there is a match in one of the tables.
| SELECT a.id id_a, a.fruit fruit_a, b.id id_b, b.fruit fruit_b FROM basket_a a FULL JOIN basket_b b ON a.fruit = b.fruit WHERE a.id IS NULL OR b.id IS NULL; |
Output :
| id_a |
fruit_a |
id_b |
fruit_b |
| 3 |
banana |
null |
null |
| 4 |
cucumber |
null |
null |
| null |
null |
3 |
watermelon |
| null |
null |
4 |
pear |
For more Manipal University Jaipur CSE-IV Sem DBMS Lab Experiments CLICK HERE
| 
| 
