Mysql left join inner join outer join

"left join" will preserve all results from the first (left) table even if there is no correspondance to the right

set names utf8;
select node.nid,node_counter.nid,node.title,node_counter.totalcount from johnpfe5_physics.node left join johnpfe5_physics.node_counter on node.nid = node_counter.nid;

an "inner join", usually the default join type, compares all items in Tables A and B

Where there is a match (of the join predicate) the results are all the rows

SELECT *
FROM   employee 
       INNER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name


SELECT EMPNO, LASTNAME, PROJNO
    FROM CORPDATA.EMPLOYEE INNER JOIN CORPDATA.PROJECT
          ON EMPNO = RESPEMP
    WHERE LASTNAME > 'S'


NOTE THAT THERE IS AN "IMPLICIT INNER JOIN" IN THE FOLLOWING STATEMENT

SELECT *  
FROM   employee, department 
WHERE  employee.DepartmentID = department.DepartmentID


SELECT * FROM oauth_clients INNER JOIN oauth_sessions on oauth_clients.id = oauth_sessions.client_id WHERE oauth_clients.type=2 AND oauth_sessions.name LIKE 'http%';

UPDATE oauth_clients INNER JOIN oauth_sessions on oauth_clients.id = oauth_sessions.client_id SET type=4 WHERE oauth_clients.type=2 AND oauth_sessions.name LIKE 'http%';

The "equi join" is a special case of an inner join where the predicate can only be "equals"

SELECT *
FROM   employee 
       INNER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

Note that the "USING" command is similar

SELECT *
FROM   employee 
       INNER JOIN department 
          USING (DepartmentID)

BUT IT WILL NOT RETURN two separate columns, it will only return DepartmentID (instead of both employee.DepartmentID and department.DepartmentID)

AN EVEN MORE SPECIAL CASE IS A "NATURAL JOIN"

SELECT *
FROM   employee NATURAL JOIN department

this compares both tables and ALL COLUMNS WITH THE SAME NAME are used as equi joins (predicate aka comparison if they're equal)

a cross join combines every row in B with every row in A. (you can still add a WHERE clause)

left outer join = all the records of Table A and any matched rows from Table B (thus there will most likely be a lot of NULLS from the B columns!)

right outer join = all records from Table B with matched rows from Table A

full outer join = all records from both tables with NULL when not matched!

Mysql left join inner join outer join

Published

Category

~323 words

Tags