In an inner join, the query condition is in the ON or WHERE clause, which has the same effect

SELECT e.ename, d.dname 
FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno 
AND d.deptno = 10
Copy the code

Because the SQL statement is an inner join, all conditions in the ON clause must be met in order for the data to be queried

SELECT e.ename, d.dname 
FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno 
WHERE d.deptno = 10
Copy the code

The results of the above two SQL statements are the same

In the outer join, the query condition is written in the ON or WHERE clause, which has different effects

SELECT e.ename, d.dname 
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno 
AND d.deptno = 10
Copy the code

T_emp (left), T_DEPT (right), join (left), emP (left), and T_DEPT (right); Therefore, the condition written in the on clause of the outer join is not required to meet the condition to query the record, do not meet the condition will also query the record.

SELECT e.ename, d.dname 
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno 
WHERE d.deptno = 10
Copy the code

If you write a condition in the WHERE clause, then the records that match the condition will be queried, instead of preserving all the data in the left table.