The most important aspect of SQL is it’s relational features,
where the data available in the tables combined with the necessary relations.
very frequently we will be coming across the situation, we have to combine two tables.
- make sure that you have the same name and same data type in both the tables.
- the keywords LEFT and RIGHT are not absolute. they operate within the context of the given statement.
- we can reverse the order of the tables and reverse the keywords and the result would be the same.
- if the type of join is not specified as inner or outer then it will be executed as an INNER JOIN
Demo dbs
create table english
(
id int(1),
word varchar(5)
);
create table hindi
(
id int(1),
word varchar(5)
);
insert into english values (0, ‘zero’);
insert into english values (1, ‘one’);
insert into english values (2, ‘two’);
insert into english values (3, ‘three’);
insert into hindi values (5, ‘five’);
insert into hindi values (1, ‘ek’);
insert into hindi values (2, ‘do’);
insert into hindi values (3, ‘theen’);
insert into hindi values (4, ‘char’);
for those who doesnt understand, the hindi db gives the corresponding terms in english db
CARTESIAN JOIN
A cartesian join is when you join every row of one table to every row of another table.
select * from hindi, english;
mysql> select * from hindi, english;
+——+——-+——+——-+
| id | word | id | word |
+——+——-+——+——-+
| 1 | ek | 1 | one |
| 1 | ek | 2 | two |
| 1 | ek | 3 | three |
| 1 | ek | 0 | zero |
| 2 | do | 1 | one |
| 2 | do | 2 | two |
| 2 | do | 3 | three |
| 2 | do | 0 | zero |
| 3 | theen | 1 | one |
| 3 | theen | 2 | two |
| 3 | theen | 3 | three |
| 3 | theen | 0 | zero |
| 5 | five | 1 | one |
| 5 | five | 2 | two |
| 5 | five | 3 | three |
| 5 | five | 0 | zero |
| 4 | char | 1 | one |
| 4 | char | 2 | two |
| 4 | char | 3 | three |
| 4 | char | 0 | zero |
+——+——-+——+——-+
20 rows in set (0.00 sec)
INNER JOIN
Inner join gives the all row from both the tables, where there is a match.
If there are any rows those dint fall in the condition are simply IGNORED
select english.id, english.word, hindi.word from english inner join hindi on english.id=hindi.id;
+——+——-+——-+
| id | word | word |
+——+——-+——-+
| 1 | one | ek |
| 2 | two | do |
| 3 | three | theen |
+——+——-+——-+
3 rows in set (0.00 sec)
select * from hindi join english using (id);
+——+——-+——-+
| id | word | word |
+——+——-+——-+
| 1 | ek | one |
| 2 | do | two |
| 3 | theen | three |
+——+——-+——-+
3 rows in set (0.00 sec)
LEFT JOIN
left outer join gives ALL the rows from the first table, and selected rows from second table those fall in the condition.
rows of second table, those dint fall in the condition are IGNORED.
select english.id, english.word, hindi.word from english left join hindi on english.id=hindi.id;
+——+——-+——-+
| id | word | word |
+——+——-+——-+
| 1 | one | ek |
| 2 | two | do |
| 3 | three | theen |
| 0 | zero | NULL |
+——+——-+——-+
4 rows in set (0.00 sec)
RIGHT JOIN
right outer join gives ALL the rows from the second table, and selected rows from first table those fall in the condition.
rows of first table, those dint fall in the condition are IGNORED.
select english.id, english.word, hindi.word from english right join hindi on english.id=hindi.id;
+——+——-+——-+
| id | word | word |
+——+——-+——-+
| 1 | one | ek |
| 2 | two | do |
| 3 | three | theen |
| NULL | NULL | five |
| NULL | NULL | char |
+——+——-+——-+
5 rows in set (0.00 sec)
