0
Submit

MYSQL


MySQL


Admission Enquiry Form

  

Joins in MySql



What are Joins?

MySQL JOINS are used to retrieve data from multiple tables. These are performed whenever we need to fetch records from two or more tables.MySQL JOINS are used with SELECT statement.This is usually much more efficient than trying to perform multiple queries and combining them later.




Types of Joins?

There are four types of joins:

  1. Cartesian Join
  2. Equi Join
  3. Self Join
  4. Outer Join
    1. Left Join
    2. Right Join
    3. Full Outer Join

Here we will refer two tables to implement the joins:

1. Dept table with select query:

select * from DEPT;

Output:

2. Emp table with select query:

select * from EMP;

Output:




Cartesian Join

In a CARTESIAN JOIN there is a join for each row of one table to every row of another table.This join returns all records from both tables (table1 and table2). This usually happens when the matching column or WHERE condition is not specified.The CARTESIAN JOIN is also known as CROSS JOIN.

  1. In the absence of a WHERE condition the CARTESIAN JOIN will behave like a CARTESIAN PRODUCT . i.e., the number of rows in the result-set is the product of the number of rows of the two tables.
  2. In the presence of WHERE condition this JOIN will function like a EQUI JOIN.

Query to implement CARTESIAN JOIN:

select ename,sal,dname,loc,emp.deptno from emp,dept;

Output:




Equi Join

EQUI JOIN creates a JOIN for equality or matching column(s) values of the relative tables.We can use the equal sign (=) comparison operator to refer to equality in the WHERE clause.

Query to implement EQUI JOIN using where clause:

select ename,sal,dname,loc,emp.deptno from emp,dept where emp.deptno=dept.deptno;

or

Query to implement EQUI JOIN using join and on clause:

select ename,sal,emp.deptno,dname,loc from emp join dept on emp.deptno=dept.deptno;

Output:




Self Join

In MySQL, a self join is a specific type of join where a table is joined with itself. This can be useful when you have a table with a hierarchical structure or when you need to compare rows within the same table.

Query to implement SELF JOIN with where clause:

select worker.ename "Worker",manager.ename "Manager" from emp worker,emp manager where worker.mgr=manager.empno;

or

Query to implement SELF JOIN with join and on clause:

select worker.ename "Worker",manager.ename "Manager" from emp worker join emp manager on worker.mgr=manager.empno;

Output:




Left Outer Join

The left outer join is used to combine two different tables.The left outer join returns all record from left table and matching records from the right table.But we will get only those records from the right table, which have the corresponding key in the left table.
On a matching element not found in right table, NULL is represented in that case.

Query to implement LEFT OUTER JOIN :

select ename,sal,job,loc,dname,dept.deptno from emp left outer join dept on dept.deptno=emp.deptno;

Output:




Right Outer Join

The right outer join is used to combine two different tables.The right outer join returns all record from right table and matching records from the left table.But we will get only those records from the left table, which have the corresponding key in the right table.
On a matching element not found in left table, NULL is represented in that case.

Query to implement RIGHT OUTER JOIN :

select ename,sal,job,loc,dname,dept.deptno from emp right outer join dept on dept.deptno=emp.deptno;

Output: