By MrM


2009-01-15 19:07:31 8 Comments

I am wondering how to differentiate all these different joins ...

9 comments

@Laxmi 2018-01-12 04:26:30

SQL JOINS difference:

Very simple to remember :

INNER JOIN only show records common to both tables.

OUTER JOIN all the content of the both tables are merged together either they are matched or not.

LEFT JOIN is same as LEFT OUTER JOIN - (Select records from the first (left-most) table with matching right table records.)

RIGHT JOIN is same as RIGHT OUTER JOIN - (Select records from the second (right-most) table with matching left table records.)

enter image description here

@philipxy 2018-08-11 09:47:24

There is a correct & relevant way to label the Venn diagram circles, but this isn't it. The circles are not the input tables. Also the result rows are not input rows, so your description is wrong there. Also this just isn't clear--you don't explain "common to both", "matched", "merged".

@HM Nayem 2017-11-02 15:47:16

At first you have to understand what does join do? We connect multiple table and get specific result from the joined tables. The simplest way to do this is cross join.

Lets say tableA has two column A and B. And tableB has three column C and D. If we apply cross join it will produce lot of meaningless row. Then we have to match using primary key to get actual data.

Left: it will return all records from left table and matched record from right table.

Right: it will return opposite to Left join. It will return all records from right table and matched records from left table.

Inner: This is like intersection. It will return only matched records from both table.

Outer: And this is like union. It will return all available record from both table.

Some times we don't need all of the data, and also we should need only common data or records. we can easily get it using these join methods. Remember left and right join also are outer join.

You can get all records just using cross join. But it could be expensive when it comes to millions of records. So make it simple by using left, right, inner or outer join.

thanks

@BradC 2009-01-15 19:18:49

Simple Example: Lets say you have a Students table, and a Lockers table. In SQL, the first table you specify in a join, Students, is the LEFT table, and the second one, Lockers, is the RIGHT table.

Each student can be assigned to a locker, so there is a LockerNumber column in the Student table. More than one student could potentially be in a single locker, but especially at the beginning of the school year, you may have some incoming students without lockers and some lockers that have no students assigned.

For the sake of this example, lets say you have 100 students, 70 of which have lockers. You have a total of 50 lockers, 40 of which have at least 1 student and 10 lockers have no student.

INNER JOIN is equivalent to "show me all students with lockers".
Any students without lockers, or any lockers without students are missing.
Returns 70 rows

LEFT OUTER JOIN would be "show me all students, with their corresponding locker if they have one".
This might be a general student list, or could be used to identify students with no locker.
Returns 100 rows

RIGHT OUTER JOIN would be "show me all lockers, and the students assigned to them if there are any".
This could be used to identify lockers that have no students assigned, or lockers that have too many students.
Returns 80 rows (list of 70 students in the 40 lockers, plus the 10 lockers with no student)

FULL OUTER JOIN would be silly and probably not much use.
Something like "show me all students and all lockers, and match them up where you can"
Returns 110 rows (all 100 students, including those without lockers. Plus the 10 lockers with no student)

CROSS JOIN is also fairly silly in this scenario.
It doesn't use the linked lockernumber field in the students table, so you basically end up with a big giant list of every possible student-to-locker pairing, whether or not it actually exists.
Returns 5000 rows (100 students x 50 lockers). Could be useful (with filtering) as a starting point to match up the new students with the empty lockers.

@Joel Coehoorn 2009-01-15 20:15:38

Using your example, CROSS join would be useful as a starting point for creating locker assignments: start with all possible combinations and then use other criteria to filter results from the list.

@Eli 2009-01-15 21:24:56

Nice answer. I believe Cross Join is most often used to generate testing data from a few rows when you need a large number of records.

@Lara Dougan 2009-01-21 07:17:26

FULL OUTER JOINS can be useful when searching for orphaned data, or when comparing different versions of the same data set.

@Lara Dougan 2009-01-21 07:18:16

Since CROSS JOINS generate all possible row combination between the two tables, it may be useful when there is no defined relationship in the schema and/or allows esoteric filtering rules to be used.

@JavaRocky 2010-08-04 00:08:28

Cross join aka cartesian product

@Salman von Abbas 2012-09-28 20:23:56

Note: In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other).

@Richard Gomes 2014-02-14 14:57:33

There's also SELF JOIN, when you make a join of a table against itself. The concept can be as simple as that or you can extrapolate to something a bit more complicated, like a hierarchy, which would be a multilevel relationship of table against itself, like in the case you would like to obtain the entire family tree from a certain person, starting by that person. See this answer for more information: stackoverflow.com/questions/2319284/…

@Enissay 2014-08-01 01:19:56

You should add the NATURAL JOIN to complete the list :)

@jbmilgrom 2016-02-12 16:07:01

I think how you begin your query impacts the outcome of the join type. For example, SELECT * FROM students RIGHT OUTER JOIN lockers... would result in a different outcome than SELECT * FROM lockers RIGHT OUTER JOIN students.... Great answer, but would love to see it updated with complete SQL queries

@lfvv 2017-06-28 18:02:03

I think you could name each column of both tables in order to let the example clearer. Still, I noticed that in your example there is a locker_id in the Student table, but not student_id in the Locker table, right? Still, I got the idea.

@BradC 2017-06-28 18:25:56

@lfvv Correct, because each student can only be assigned to a single locker, but each locker could have 2 or more students. If each student could have multiple lockers, you'd need a many-to-many join table.

@lfvv 2017-06-28 18:16:58

Making it more visible might help. One example:

Table 1:

ID_STUDENT STUDENT_NAME

1               Raony
2               Diogo
3               Eduardo
4               Luiz

Table 2:

ID_STUDENT LOCKER

3               l1
4               l2
5               l3

What I get when I do:

-Inner join of Table 1 and Table 2: 

    - Inner join returns both tables merged only when the key 
      (ID_STUDENT) exists in both tables

    ID_STUDENT       STUDENT_NAME      LOCKER   

        3               Eduardo          l1
        4               Luiz             l2

-Left join of Table 1 and Table 2:

    - Left join merges both tables with all records form table 1, in 
      other words, there might be non-populated fields from table 2

    ID_ESTUDANTE    NOME_ESTUDANTE     LOCKER   

        1               Raony            -
        2               Diogo            -
        3               Eduardo          l1
        4               Luiz             l2

-Right join of table 1 and table 2:

    - Right join merges both tables with all records from table 2, in 
      other words, there might be non-populated fields from table 1

    ID_STUDENT        STUDENT_NAME     LOCKER   

        3               Eduardo          l1
        4               Luiz             l2
        5               -                l3

-Outter join of table 1 and table 2:

    - Returns all records from both tables, in other words, there
      might be non-populated fields either from table 1 or 2.

    ID_STUDENT        STUDENT_NAME     LOCKER   
        1               Raony            -
        2               Diogo            -
        3               Eduardo          l1
        4               Luiz             l2
        5               -                l3

@RussellH 2009-01-15 19:14:09

LEFT JOIN and RIGHT JOIN are types of OUTER JOINs.

INNER JOIN is the default -- rows from both tables must match the join condition.

@nbro 2016-06-13 16:00:34

I can't believe how this answer has some many upvotes and at the same time it is so incomplete.

@RussellH 2017-07-14 23:06:47

I think it was a better answer to the original question.

@PethÅ‘ Jonatán 2015-02-15 20:20:53

Inner join: Only show rows, when has it data from both of the tables.

Outer join: (left/right): Show the all result from the left / right table with the paired row(s), if it exists or not.

@Joel Coehoorn 2009-01-15 19:24:29

There are three basic types of join:

  • INNER join compares two tables and only returns results where a match exists. Records from the 1st table are duplicated when they match multiple results in the 2nd. INNER joins tend to make result sets smaller, but because records can be duplicated this isn't guaranteed.
  • CROSS join compares two tables and return every possible combination of rows from both tables. You can get a lot of results from this kind of join that might not even be meaningful, so use with caution.
  • OUTER join compares two tables and returns data when a match is available or NULL values otherwise. Like with INNER join, this will duplicate rows in the one table when it matches multiple records in the other table. OUTER joins tend to make result sets larger, because they won't by themselves remove any records from the set. You must also qualify an OUTER join to determine when and where to add the NULL values:
    • LEFT means keep all records from the 1st table no matter what and insert NULL values when the 2nd table doesn't match.
    • RIGHT means the opposite: keep all records from the 2nd table no matter what and insert NULL values whent he 1st table doesn't match.
    • FULL means keep all records from both tables, and insert a NULL value in either table if there is no match.

Often you see will the OUTER keyword omitted from the syntax. Instead it will just be "LEFT JOIN", "RIGHT JOIN", or "FULL JOIN". This is done because INNER and CROSS joins have no meaning with respect to LEFT, RIGHT, or FULL, and so these are sufficient by themselves to unambiguously indicate an OUTER join.

Here is an example of when you might want to use each type:

  • INNER: You want to return all records from the "Invoice" table, along with their corresponding "InvoiceLines". This assumes that every valid Invoice will have at least one line.
  • OUTER: You want to return all "InvoiceLines" records for a particular Invoice, along with their corresponding "InventoryItem" records. This is a business that also sells service, such that not all InvoiceLines will have an IventoryItem.
  • CROSS: You have a digits table with 10 rows, each holding values '0' through '9'. You want to create a date range table to join against, so that you end up with one record for each day within the range. By CROSS-joining this table with itself repeatedly you can create as many consecutive integers as you need (given you start at 10 to 1st power, each join adds 1 to the exponent). Then use the DATEADD() function to add those values to your base date for the range.

@matpop 2017-04-28 11:12:38

Nice. I'd only add that usually if you only write 'JOIN' it means INNER JOIN.

@j_random_hacker 2009-01-15 19:13:21

There are only 4 kinds:

  1. Inner join: The most common type. An output row is produced for every pair of input rows that match on the join conditions.
  2. Left outer join: The same as an inner join, except that if there is any row for which no matching row in the table on the right can be found, a row is output containing the values from the table on the left, with NULL for each value in the table on the right. This means that every row from the table on the left will appear at least once in the output.
  3. Right outer join: The same as a left outer join, except with the roles of the tables reversed.
  4. Full outer join: A combination of left and right outer joins. Every row from both tables will appear in the output at least once.

A "cross join" or "cartesian join" is simply an inner join for which no join conditions have been specified, resulting in all pairs of rows being output.

Thanks to RusselH for pointing out FULL joins, which I'd omitted.

@SQLMenace 2009-01-15 19:15:15

what about full outer join and cross join (Cartesian product)?

@RussellH 2009-01-15 19:16:16

full is really the equivalent of two outer joins

@Paul Tomblin 2009-01-15 19:19:07

FULL is what you get when you screw up your inner join, and then you ask a question here "why am I getting N^2 rows instead of N"? Then everybody gets CROSS at you.

@JoshBerke 2009-01-15 19:15:22

Check out Join (SQL) on Wikipedia

  • Inner join - Given two tables an inner join returns all rows that exist in both tables
  • left / right (outer) join - Given two tables returns all rows that exist in either the left or right table of your join, plus the rows from the other side will be returned when the join clause is a match or null will be returned for those columns

  • Full Outer - Given two tables returns all rows, and will return nulls when either the left or right column is not there

  • Cross Joins - Cartesian join and can be dangerous if not used carefully

Related Questions

Sponsored Content

16 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

15 Answered Questions

[SOLVED] How to do a FULL OUTER JOIN in MySQL?

27 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

22 Answered Questions

[SOLVED] LEFT OUTER JOIN in LINQ

  • 2010-08-04 11:18:21
  • Toy
  • 727300 View
  • 559 Score
  • 22 Answer
  • Tags:   c# linq join

11 Answered Questions

[SOLVED] INNER JOIN ON vs WHERE clause

6 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

13 Answered Questions

[SOLVED] How to join (merge) data frames (inner, outer, left, right)

26 Answered Questions

[SOLVED] What is the difference between UNION and UNION ALL?

  • 2008-09-08 15:19:33
  • Brian G
  • 1352587 View
  • 1450 Score
  • 26 Answer
  • Tags:   sql union union-all

Sponsored Content