join in sql

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

SQL Join statement is used to combine data or rows from two or more tables based on a common field between them.

Different types of Joins are as follows: 

INNER JOIN

LEFT JOIN

RIGHT JOIN

NATURAL JOIN

FULL JOIN

create database maxx;

use maxx

create table emp

    ->(

    -> empid int primary key,

    -> name varchar(20),

    -> gender varchar(7),

    -> age int,

    -> salary int

    -> );

create table dept

    ->(

    -> deptid int ,

    -> empid int ,

    -> dept varchar(20),

    -> city varchar(20),

    -> pincode int

    -> );

insert into emp values

    -> (101,"sagar","male",22,80000),

    -> (102,"amrita","female",40,70000),

    -> (103,"umesh","male",24,90000),

    -> (104,"amit","male",21,40000),

    -> (105,"ankita","female",56,70000),

    -> (106,"sumit","male",30,50000),

    -> (107,"geeta","female",48,75000),

    -> (108,"namit","male",38,25000);

insert into dept values

    -> (21,"205","hr","banglore",560087),

    -> (23,"103","developer","delhi",890034),

    -> (23,"106","developer","pune",890054),

    -> (22,"102","sales","banglore",870054),

    -> (21,"104","hr","pune",980034),

    -> (22,"108","sales","mumbai",990794),

    -> (24,"110","analyst","ahemdabad",910894),

    -> (25,"115","analyst","pune",229834);

select * from emp;

 select * from dept;

LEFT JOIN OR OUTER LEFT JOIN

===========================

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

Q1.display name, age, and gender of all employees where empid of emp table is equal to empid of dept table ?

 select * from emp left join dept on emp.empid=dept.empid;

RIGHT JOIN OR RIGHT OUTER JOIN

==============================

SYNTAX:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

Q1.display common details from emp table and from dept table where empid of emp table is equal to empid of dept table 

select * from emp RIGHT join dept on emp.empid=dept.empid;

INNER JOIN OR JOIN

==================

The INNER JOIN keyword selects all rows from both the tables as long as the condition is satisfied. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same. 

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

show all details from ep and dept where empid of emp is equal to empid

of dept .

select * from emp inner join dept on emp.empid=dept.empid;

NATURAL JOIN

============

show all details from ep and dept where empid of emp is equal to 
empid of dept BUT EMPID COLUMN MUST DISPLAY ONLY ONCE.