"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!