MySQL - JOINS

Table 1 - DEPT (Parent table)

Table 2 - EMP (Child table)

Joins

• All the data is not stored in one table; data is stored in multiple tables.
• If you want to view/combine the columns of 2 or more tables, then you will have to write a JOIN.

-- dept: driven table
-- emp: driving table
Select dname, ename from emp, dept
where dept.deptno = emp.deptno;

-- common column in both tables
-- tablename.columnname -> dept.deptno
-- tablename.columnname -> emp.deptno 

Note: Driving table is always less row than driven table for fast performance.

Note: outer loop must have less number of rows than inner loop.

1st time scan see deptno and scan in emp table
2nd time scan see deptno and scan in emp table
3rd time scan see deptno and scan in emp table
because dept table have 3 rows. 
Therefore 3 times full table scan.

• To make the output more presentable use order by clause.

-- Fast
-- dept: driving table, other loop, less rows
-- emp: driven table, inner loop, more rows
Select dname, ename from emp, dept
where dept.deptno = emp.deptno
order by 1;

-- Slow
-- emp: driving table, other loop, more rows
-- dept: driven table, inner loop, less rows
Select dname, ename from dept, emp
where emp.deptno = dept.deptno
order by 1;

Note: In order for join to work faster, preferably the driving table should be table with lesser number of rows.

• The common column in both the tables (i.e. deptno) the column name need not be the same in both the tables because the same column can have a different meaning in the order table.
• Example: Export in one table = Import in another table.