×
☰ See All Chapters

MySQL equi join

When we use “=” (equal) operator as join condition, then it is called as equi join. There are three types of equi joins:

  1. Self Join 

  2. Inner Join 

  3. Outer Join 

For all the examples throughout this chapter we use the below tables:

mysql-equi-join-0
 

CREATE TABLE DEPT

       (DEPTNO NUMERIC(2) ,

        DNAME VARCHAR(14) ,

        LOC VARCHAR(13),

CONSTRAINT PK_DEPT PRIMARY KEY(DEPTNO)

 ) ;

 

CREATE TABLE EMP

       (EMPNO NUMERIC(4) ,

        ENAME VARCHAR(10),

        JOB VARCHAR(9),

        MGR NUMERIC(4),

        HIREDATE DATE,

        SAL NUMERIC(7,2),

        COMM NUMERIC(7,2),

        DEPTNO NUMERIC(2),

 CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO),

CONSTRAINT PK_EMP PRIMARY KEY(EMPNO)

 

);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');

INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');

INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

 

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,NULL,20);

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-5-23',1100,NULL,20);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);

COMMIT;

Self Join

  • The self join joins a table to itself, as if the table were two tables, temporarily renaming at least one table in the SQL statement using a table alias. 

  • Self joins are useful when all the data you want to retrieve resides in one table, but you must somehow compare records in the table to other records in the table. For example suppose you have a table that stores an employee identification number, the employee’s name, and the employee identification number of the employee’s manager. You might want to produce a list of all employees and their managers’ names. 

SELECT E.ENAME, E1.ENAME AS MANAGER

FROM EMP E JOIN EMP E1

ON E.MGR = E1.EMPNO;

mysql-equi-join-1
 

Inner join

In the inner join only the matching records will be selected and joined in the result.

SELECT D.DNAME, E.ENAME                  

FROM EMP E INNER JOIN DEPT D

ON D.DEPTNO = E.DEPTNO;

mysql-equi-join-2
 

Outer join

 In an outer join matching records will be selected plus extra records will be selected. Outer join is the result of inner join plus extra records.

Outer join = inner join + extra records.

Extra records are the rows which cannot match the condition due to null values.

There are three types of outer joins:

Left outer join

Left outer join will select all matching records plus extra records in the left table.

Left outer join = inner join + extra records in the left table.

mysql-equi-join-3
 

SELECT D.DNAME, E.ENAME                  

FROM EMP E LEFT OUTER JOIN DEPT D

ON D.DEPTNO = E.DEPTNO;

mysql-equi-join-4
 

SELECT D.DNAME, E.ENAME

FROM DEPT D LEFT OUTER JOIN EMP E

ON D.DEPTNO = E.DEPTNO;

mysql-equi-join-5
 

Right outer join

Right outer join will select all matching records plus extra records in the right table.

Right outer join = inner join + extra records in right table.

mysql-equi-join-6
 

SELECT D.DNAME, E.ENAME                  

FROM EMP E RIGHT OUTER JOIN DEPT D

ON D.DEPTNO = E.DEPTNO;

mysql-equi-join-7
 

SELECT D.DNAME, E.ENAME

FROM DEPT D RIGHT OUTER JOIN EMP E

ON D.DEPTNO = E.DEPTNO;

mysql-equi-join-8
 

Full outer join

Full outer join will select all matching records plus extra records in both tables.

Full outer join = inner join + extra records in both tables. This is not supported in MySQL.

Full outer join in MySQL is achieved using UNION.

SELECT E.ENAME , D.DNAME, E.JOB

FROM DEPT D LEFT OUTER JOIN EMP E

ON D.DEPTNO = E.DEPTNO

UNION

SELECT E.ENAME , D.DNAME, E.JOB

FROM DEPT D RIGHT OUTER JOIN EMP E

ON D.DEPTNO = E.DEPTNO;

 

---------------OR----------------

           

SELECT E.ENAME , D.DNAME, E.JOB

FROM DEPT D LEFT  JOIN EMP E

ON D.DEPTNO = E.DEPTNO

UNION

SELECT E.ENAME , D.DNAME, E.JOB

FROM DEPT D RIGHT  JOIN EMP E

ON D.DEPTNO = E.DEPTNO;

mysql-equi-join-9
 
All Chapters
Author