Join In SQL

Join In SQL

1. Is use to combine two or multiple tables together and generate the result.
2. Joins always use with a Select Query.
3. Types Of Join In SQL
   1. Cross Join (Cartesian Join)
   2. Inner Join
   3. Outer Join
      1. Left Outer Join
      2. Right Outer Join
      3. Full Join

Example:-

create table empinfo(

id int primary key,

name varchar(20),

contact varchar(10),

addid int,

deptid int,

FOREIGN KEY(addid) references addinfo(id),

FOREIGN KEY(deptid) references deptinfo(id));

insert into deptinfo values(1,’Development’),(2,’Testing’),(3,’HR’),(4,’Account’);

insert into addinfo values(1,’Pune’,’456454′),(2,’Pune’,’336454′),(3,’Mumbai’,’445554′);

insert into empinfo values(1, ‘A’, ‘9988776656’,2 ,1);

insert into empinfo values(2, ‘C’, ‘7788776656’,1 ,1),(3, ‘D’, ‘7888776656’,3 ,2);

insert into empinfo(id,name,contact,addid) values(4, ‘X’, ‘7788773234’,2 ),(5, ‘Y’, ‘7888776655’,3);

mysql> select * from deptinfo;

+—-+————-+

| id | name        |

+—-+————-+

|  1 | Development |

|  2 | Testing     |

|  3 | HR          |

|  4 | Account     |

+—-+————-+

4 rows in set (0.00 sec)

mysql> select * from addinfo;

+—-+——–+———+

| id | city   | pincode |

+—-+——–+———+

|  1 | Pune   | 456454  |

|  2 | Pune   | 336454  |

|  3 | Mumbai | 445554  |

+—-+——–+———+

3 rows in set (0.00 sec)

mysql> select * from empinfo;

+—-+——+————+——-+——–+

| id | name | contact    | addid | deptid |

+—-+——+————+——-+——–+

|  1 | A    | 9988776656 |     2 |      1 |

|  2 | C    | 7788776656 |     1 |      1 |

|  3 | D    | 7888776656 |     3 |      2 |

|  4 | X    | 7788773234 |     2 |   NULL |

|  5 | Y    | 7888776655 |     3 |   NULL |

+—-+——+————+——-+——–+

5 rows in set (0.00 sec)

Cross Join

1. Join with every row of one table with another table.
2. There is no where clause in the cross join.

mysql> select * from empinfo CROSS JOIN addinfo;

+—-+——+————+——-+——–+—-+——–+———+

| id | name | contact    | addid | deptid | id | city   | pincode |

+—-+——+————+——-+——–+—-+——–+———+

|  1 | A    | 9988776656 |     2 |      1 |  3 | Mumbai | 445554  |

|  1 | A    | 9988776656 |     2 |      1 |  2 | Pune   | 336454  |

|  1 | A    | 9988776656 |     2 |      1 |  1 | Pune   | 456454  |

|  2 | C    | 7788776656 |     1 |      1 |  3 | Mumbai | 445554  |

|  2 | C    | 7788776656 |     1 |      1 |  2 | Pune   | 336454  |

|  2 | C    | 7788776656 |     1 |      1 |  1 | Pune   | 456454  |

|  3 | D    | 7888776656 |     3 |      2 |  3 | Mumbai | 445554  |

|  3 | D    | 7888776656 |     3 |      2 |  2 | Pune   | 336454  |

|  3 | D    | 7888776656 |     3 |      2 |  1 | Pune   | 456454  |

|  4 | X    | 7788773234 |     2 |   NULL |  3 | Mumbai | 445554  |

|  4 | X    | 7788773234 |     2 |   NULL |  2 | Pune   | 336454  |

|  4 | X    | 7788773234 |     2 |   NULL |  1 | Pune   | 456454  |

|  5 | Y    | 7888776655 |     3 |   NULL |  3 | Mumbai | 445554  |

|  5 | Y    | 7888776655 |     3 |   NULL |  2 | Pune   | 336454  |

|  5 | Y    | 7888776655 |     3 |   NULL |  1 | Pune   | 456454  |

|  6 | Z    | 9988771122 |  NULL |   NULL |  3 | Mumbai | 445554  |

|  6 | Z    | 9988771122 |  NULL |   NULL |  2 | Pune   | 336454  |

|  6 | Z    | 9988771122 |  NULL |   NULL |  1 | Pune   | 456454  |

+—-+——+————+——-+——–+—-+——–+———+

18 rows in set (0.01 sec)

Inner Join

1. The rows from both the table will be selected if they are matches with the condition
2. Syntax:

SELECT column(s)

from Table1 INNER JOIN Table1

ON Condition

INNER JOIN table3

ON Condition

INNER JOIN table3

ON Condition

mysql> select * from empinfo INNER JOIN addinfo ON empinfo.addid=addinfo.id;

+—-+——+————+——-+——–+—-+——–+———+

| id | name | contact    | addid | deptid | id | city   | pincode |

+—-+——+————+——-+——–+—-+——–+———+

|  2 | C    | 7788776656 |     1 |      1 |  1 | Pune   | 456454  |

|  1 | A    | 9988776656 |     2 |      1 |  2 | Pune   | 336454  |

|  4 | X    | 7788773234 |     2 |   NULL |  2 | Pune   | 336454  |

|  3 | D    | 7888776656 |     3 |      2 |  3 | Mumbai | 445554  |

|  5 | Y    | 7888776655 |     3 |   NULL |  3 | Mumbai | 445554  |

+—-+——+————+——-+——–+—-+——–+———+

5 rows in set (0.00 sec)

mysql> select name,contact,city,pincode from empinfo INNER JOIN addinfo ON empinfo.addid=addinfo.id AND empinfo.name=’A’;

+——+————+——+———+

| name | contact    | city | pincode |

+——+————+——+———+

| A    | 9988776656 | Pune | 336454  |

+——+————+——+———+

1 row in set (0.00 sec)

mysql> select emp.id,emp.name,contact,city,pincode,d.name as department

-> from empinfo emp INNER JOIN addinfo a

-> ON emp.addid=a.id

-> INNER JOIN deptInfo d

-> ON emp.deptId = d.id;

+—-+——+————+——–+———+————-+

| id | name | contact    | city   | pincode | department  |

+—-+——+————+——–+———+————-+

|  2 | C    | 7788776656 | Pune   | 456454  | Development |

|  1 | A    | 9988776656 | Pune   | 336454  | Development |

|  3 | D    | 7888776656 | Mumbai | 445554  | Testing     |

+—-+——+————+——–+———+————-+

3 rows in set (0.00 sec)

Left Join

1. In the LEFT JOIN all the (matching and non-matching) records selected from the left side table and only the matching records of the right side table will be selected.
2. Syntax:

SELECT column(s)

from Table1 LEFT JOIN Table2

ON Condition

LEFT JOIN table3

ON Condition

LEFT JOIN table3

ON Condition

mysql> select emp.id,emp.name,contact,city,pincode

-> from empinfo emp LEFT JOIN addinfo a

-> ON emp.addid=a.id;

+—-+——+————+——–+———+

| id | name | contact    | city   | pincode |

+—-+——+————+——–+———+

|  1 | A    | 9988776656 | Pune   | 336454  |

|  2 | C    | 7788776656 | Pune   | 456454  |

|  3 | D    | 7888776656 | Mumbai | 445554  |

|  4 | X    | 7788773234 | Pune   | 336454  |

|  5 | Y    | 7888776655 | Mumbai | 445554  |

|  6 | Z    | 9988771122 | NULL   | NULL    |

+—-+——+————+——–+———+

6 rows in set (0.00 sec)

Right Join

1. In the RIGHT JOIN all the (matching and non-matching) records selected from the right side table and only the matching records of the left side table will be selected.
2. Syntax:

SELECT column(s)

from Table1 RIGHT JOIN Table2

ON Condition

RIGHT JOIN table3

ON Condition

RIGHT JOIN table3

ON Condition

mysql> select emp.id,emp.name,contact,d.name as department

-> from empinfo emp RIGHT JOIN deptinfo d

-> ON emp.deptid = d.id;

+——+——+————+————-+

| id   | name | contact    | department  |

+——+——+————+————-+

|    2 | C    | 7788776656 | Development |

|    1 | A    | 9988776656 | Development |

|    3 | D    | 7888776656 | Testing     |

| NULL | NULL | NULL       | HR          |

| NULL | NULL | NULL       | Account     |

+——+——+————+————-+

5 rows in set (0.00 sec)

Full Join

1. In the FULL JOIN all the (matching and non-matching) records selected from both the tables.
2. Syntax:

SELECT column(s)

from Table1 FULL JOIN Table2

ON Condition

FULL JOIN table3

ON Condition

FULL JOIN table3

ON Condition

Example: (FULL OUTER join is not supported in MYSQL it can be use in Oracle, Postgrese)

select emp.id,emp.name,contact,d.name as department

from empinfo emp FULL JOIN deptinfo d

ON emp.deptid = d.id;

Join Telegram : Click Here

All Full Stack Java Study Material

Job’s For Fresher

Share This Information To Your Friends and Your College Group’s, To Help Them !