Login with  Log in with facebook
Hiring Manager? SIGN UP HERE
0

I am always confused between them, hope someone can explain me these concepts in layman terms.

Eric Smith
09/08/2011 at 09:12
1 Answer
1
0

Hey Eric,

I will first explain the concept of a join in general, and then I will try to explain the difference between the different kinds of joins.

The idea of a join is simply to "merge" the content (rows) of 2 relation tables on some optional condition. For instance, the rows of an employees table where each row has a column "department_id" which corresponds to an "id" column in a departments table. You could then join the employees and departments table on "department_id = id" to query for each employee in which department name he/she works. No direct relation is necessary between tables (although most of the time you will be joining tables on foreign key - parent key relationships).

The different kinds of join then are (I use ANSI syntaxes here):

JOIN ON/USING: This is a join where you merge the rows of two tables on some condition defined in the on/using clause, rows where either of the column values are null are NOT added to the resultset

NATURAL JOIN: This is the same as a normal join, only the condition on which joined is automatically resolved, the rows in the tables are joined on columns with the same name (e.g.: an employees table with a department_id column and a departments table with an department_id column)

CROSS JOIN: This is a join without a condition on how the rows should be merged, this means that each row in the first table will be joined with each row in the second table (i.e.: if you have 10 rows in employees table and 10 rows in the department table, you will have 100 rows when cross joining them)

LEFT (OUTER) JOIN ON: This is a join where the first mentioned table (the left one)'s rows will be added to the final resultset even if the column values in the other table are null (These rows would normally NOT be added to the final result set since no value every equals null)

RIGHT (OUTER) JOIN ON: This is a join where the second mentioned table (the right one)'s rows will be added to the final resultset even if the column values in the other table are null (These rows would normally NOT be added to the final result set since no value every equals null)

FULL (OUTER) JOIN ON: This is a join where both mentioned tables's rows will be added to the final resultset even if the column values in the other table are null.

Note that if you join multiple tables, each new join will join on all rows in the previous total resultset.

I hope this clears things up for you and if not I will try to give some examples.

Kind Regards,

Geert

Geert Guldentops
09/11/2011 at 13:52

If you want to post any answer to this forum then you need to log in.
Schedule a Demo

Schedule a Demo with us

Name *
Email *
Phone *
Company *
Details