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