What is the difference between INNER JOIN and OUTER JOIN

Question

Also how do LEFT JOIN  RIGHT JOIN and FULL JOIN fit in

User · Answer

Having criticized the much-loved red-shaded Venn diagram, I thought it only fair to post my own attempt.

Although @Martin Smith's answer is the best of this bunch by a long way, his only shows the key column from each table, whereas I think ideally non-key columns should also be shown.

The best I could do in the half hour allowed, I still don't think it adequately shows that the nulls are there due to absence of key values in TableB or that OUTER JOIN is actually a union rather than a join:

User · Answer

Inner Join Retrieve the matched rows only  that is  A intersect B   SELECT   FROM dbo Students S INNER JOIN dbo Advisors A     ON S Advisor ID   A Advisor ID   Left Outer Join Select all records from the first table  and any records in the second table that match the joined keys   SELECT   FROM dbo Students S LEFT JOIN dbo Advisors A     ON S Advisor ID   A Advisor ID   Full Outer Join Select all records from the second table  and any records in the first table that match the joined keys   SELECT   FROM dbo Students S FULL JOIN dbo Advisors A     ON S Advisor ID   A Advisor ID   References  Inner and outer joins SQL examples and the Join block  SQL  JOINS

User · Answer

I don t see much details about performance and optimizer in the other answers   Sometimes it is good to know that only INNER JOIN is associative which means the optimizer has the most option to play with it  It can reorder the join order to make it faster keeping the same result  The optimizer can use the most join modes   Generally it is a good practice to try to use INNER JOIN instead of the different kind of joins   Of course if it is possible considering the expected result set    There are a couple of good examples and explanation here about this strange associative behavior    Are left outer joins associative  Does the join order matter in SQL

User · Answer

Inner join    A join is combining the rows from two tables  An inner join attempts to match up the two tables based on the criteria you specify in the query  and only returns the rows that match  If a row from the first table in the join matches two rows in the second table  then two rows will be returned in the results  If there   s a row in the first table that doesn   t match a row in the second  it   s not returned  likewise  if there   s a row in the second table that doesn   t match a row in the first  it   s not returned   Outer Join    A left join attempts to find match up the rows from the first table to rows in the second table  If it can   t find a match  it will return the columns from the first table and leave the columns from the second table blank  null

User · Answer

The answer is in the meaning of each one  so in the results      Note     In SQLite there is no RIGHT OUTER JOIN or FULL OUTER JOIN    And also in MySQL there is no FULL OUTER JOIN    My answer is based on above Note   When you have two tables like these    -- table1                -- table2  id   name                id   name --- -------              --- ------- 1    a1                  1    a2 2    b1                  3    b2     CROSS JOIN   OUTER JOIN   You can have all of those tables data with CROSS JOIN or just with   like this   SELECT   FROM table1  table2 -- OR  SELECT   FROM table1 CROSS JOIN table2  -- Results   id   name   id   name  --- ------ ---- ------ 1    a1     1    a2 1    a1     3    b2 2    b1     1    a2 2    b1     3    b2     INNER JOIN   When you want to add a filter to above results based on a relation like table1 id   table2 id you can use INNER JOIN   SELECT   FROM table1  table2 WHERE table1 id   table2 id -- OR  SELECT   FROM table1 INNER JOIN table2 ON table1 id   table2 id  -- Results   id   name   id   name  --- ------ ---- ------ 1    a1     1    a2     LEFT  OUTER  JOIN   When you want to have all rows of one of tables in the above result -with same relation- you can use LEFT JOIN   For RIGHT JOIN just change place of tables   SELECT   FROM table1  table2 WHERE table1 id   table2 id  UNION ALL SELECT    Null  Null FROM table1 WHERE Not table1 id In  SELECT id FROM table2  -- OR  SELECT   FROM table1 LEFT JOIN table2 ON table1 id   table2 id  -- Results   id   name   id     name  --- ------ ------ ------ 1    a1     1      a2 2    b1     Null   Null     FULL OUTER JOIN   When you also want to have all rows of the other table in your results you can use FULL OUTER JOIN   SELECT   FROM table1  table2 WHERE table1 id   table2 id UNION ALL SELECT    Null  Null FROM table1 WHERE Not table1 id In  SELECT id FROM table2  UNION ALL SELECT Null  Null    FROM table2 WHERE Not table2 id In  SELECT id FROM table1  -- OR   recommended for SQLite  SELECT   FROM table1 LEFT JOIN table2 ON table1 id   table2 id UNION ALL SELECT   FROM table2 LEFT JOIN table1 ON table2 id   table1 id WHERE table1 id IS NULL -- OR  SELECT   FROM table1 FULL OUTER JOIN table2 On table1 id   table2 id  -- Results   id     name   id     name  ----- ------ ------ ------ 1      a1     1      a2 2      b1     Null   Null Null   Null   3      b2   Well  as your need you choose each one that covers your need

User · Answer

Simplest Definitions  Inner Join  Returns matched records from both tables   Full Outer Join  Returns matched and unmatched records from both tables with null for unmatched records from Both Tables   Left Outer Join  Returns matched and unmatched records only from table on Left Side   Right Outer Join  Returns matched and unmatched records only from table on Right Side   In-Short  Matched   Left Unmatched   Right Unmatched   Full Outer Join  Matched   Left Unmatched   Left Outer Join  Matched   Right Unmatched   Right Outer Join  Matched   Inner Join

User · Answer

There is a lot of misinformation out there on this topic  including here on Stack Overflow  left join on  aka left outer join on  returns inner join on rows union all unmatched left table rows extended by nulls  right join  on aka  right outer join on  returns inner join on rows union all unmatched right table rows extended by nulls  full join on  aka full outer join on  returns inner join on rowsunion all unmatched left table rows extended by nulls union all unmatched right table rows extended by nulls   SQL Standard 2006 SQL Foundation 7 7 Syntax Rules 1  General Rules 1 b  3 c  amp  d  5 b   So don t outer join until you know what underlying inner join is involved   Find out what rows inner join returns

User · Answer

1 Inner Join  Also called as Join  It returns the rows present in both the Left table  and right table only if there is a match  Otherwise  it returns zero records   Example   SELECT   e1 emp name    e2 emp salary     FROM emp1 e1 INNER JOIN emp2 e2   ON e1 emp id   e2 emp id     2 Full Outer Join  Also called as Full Join  It returns all the rows present in both the Left table  and right table   Example   SELECT   e1 emp name    e2 emp salary     FROM emp1 e1 FULL OUTER JOIN emp2 e2   ON e1 emp id   e2 emp id     3 Left Outer join  Or simply called as Left Join  It returns all the rows present in the Left table and matching rows from the right table  if any    4 Right Outer Join  Also called as Right Join  It returns matching rows from the left table  if any   and all the rows present in the Right table     Advantages of Joins   Executes faster

User · Answer

Joins are used to combine the data from two tables  with the result being a new  temporary table  Joins are performed based on something called a predicate  which specifies the condition to use in order to perform a join   The difference between an inner join and an outer join is that an inner join will return only the rows that actually match based on the join predicate  For eg- Lets consider Employee and Location table     Inner Join - Inner join creates a new result table by combining column values of two tables  Employee and Location  based upon the join-predicate  The query compares each row of Employee with each row of Location to find all pairs of rows which satisfy the join-predicate  When the join-predicate is satisfied by matching non-NULL values  column values for each matched pair of rows of Employee and Location are combined into a result row  Here   s what the SQL for an inner join will look like   select    from employee inner join location on employee empID   location empID OR select    from employee  location where employee empID   location empID   Now  here is what the result of running that SQL would look like    Outer Join - An outer join does not require each record in the two joined tables to have a matching record  The joined table retains each record   even if no other matching record exists  Outer joins subdivide further into left outer joins and right outer joins  depending on which table s rows are retained  left or right    Left Outer Join - The result of a left outer join  or simply left join  for tables Employee and Location always contains all records of the  left  table  Employee   even if the join-condition does not find any matching record in the  right  table  Location   Here is what the SQL for a left outer join would look like  using the tables above   select    from employee left outer join location on employee empID   location empID    Use of outer keyword is optional   Now  here is what the result of running this SQL would look like    Right Outer Join - A right outer join  or right join  closely resembles a left outer join  except with the treatment of the tables reversed  Every row from the  right  table  Location  will appear in the joined table at least once  If no matching row from the  left  table  Employee  exists  NULL will appear in columns from Employee for those records that have no match in Location  This is what the SQL looks like   select   from employee right outer join location  on employee empID   location empID    Use of outer keyword is optional   Using the tables above  we can show what the result set of a right outer join would look like     Full Outer Joins - Full Outer Join or Full Join is to retain the nonmatching information by including nonmatching rows in the results of a join  use a full outer join  It includes all rows from both tables  regardless of whether or not the other table has a matching value   Image Source  MySQL 8 0 Reference Manual - Join Syntax  Oracle Join operations

User · Answer

You use INNER JOIN to return all rows from both tables where there is a match  i e  In the resulting table all the rows and columns will have values   In OUTER JOIN the resulting table may have empty columns  Outer join may be either LEFT or RIGHT   LEFT OUTER JOIN returns all the rows from the first table  even if there are no matches in the second table   RIGHT OUTER JOIN returns all the rows from the second table  even if there are no matches in the first table

User · Answer

Inner join - An inner join using either of the equivalent queries gives the intersection of the two tables  i e  the two rows they have in common  Left outer join - A left outer join will give all rows in A  plus any common rows in B  Full outer join - A full outer join will give you the union of A and B  i e  All the rows in A and all the rows in B  If something in A doesn t have a corresponding datum in B  then the B portion is null  and vice versay

User · Answer

The General Idea Please see the answer by Martin Smith for a better illustations and explanations of the different joins  including and especially differences between FULL OUTER JOIN  RIGHT OUTER JOIN and LEFT OUTER JOIN  These two table form a basis for the representation of the JOINs below   CROSS JOIN  SELECT     FROM citizen  CROSS JOIN postalcode  The result will be the Cartesian products of all combinations  No JOIN condition required   INNER JOIN INNER JOIN is the same as simply  JOIN  SELECT     FROM citizen    c   JOIN postalcode p ON c postal   p postal  The result will be combinations that satisfies the required JOIN condition   LEFT OUTER JOIN LEFT OUTER JOIN is the same as LEFT JOIN  SELECT     FROM citizen         c   LEFT JOIN postalcode p ON c postal   p postal  The result will be everything from citizen even if there are no matches in postalcode  Again a JOIN condition is required   Data for playing All examples have been run on an Oracle 18c  They re available at dbfiddle uk which is also where screenshots of tables came from  CREATE TABLE citizen  id      NUMBER                        name    VARCHAR2 20                         postal  NUMBER   --  lt -- could do with a redesign to postalcode id instead                        leader  NUMBER    CREATE TABLE postalcode  id      NUMBER                           postal  NUMBER                           city    VARCHAR2 20                            area    VARCHAR2 20     INSERT INTO citizen  id  name  postal  leader                SELECT 1   Smith   2200   null FROM DUAL         UNION SELECT 2   Green   31006  1    FROM DUAL         UNION SELECT 3   Jensen   623   1    FROM DUAL   INSERT INTO postalcode  id  postal  city  area                   SELECT 1  2200       BigCity            Geancy   FROM DUAL            UNION SELECT 2  31006      SmallTown          Snizkim  FROM DUAL            UNION SELECT 3  31006      Settlement         Moon     FROM DUAL  --  lt -- Uuh-uhh             UNION SELECT 4  78567390   LookoutTowerX89    Space    FROM DUAL   Blurry boundaries when playing with JOIN and WHERE CROSS JOIN CROSS JOIN resulting in rows as The General Idea INNER JOIN  SELECT     FROM citizen          c   CROSS JOIN postalcode p  WHERE c postal   p postal --  lt  -- The WHERE condition is limiting the resulting rows  Using CROSS JOIN to get the result of a LEFT OUTER JOIN requires tricks like adding in a NULL row  It s omitted  INNER JOIN INNER JOIN becomes a cartesian products  It s the same as The General Idea CROSS JOIN  SELECT     FROM citizen    c   JOIN postalcode p ON 1   1  --  lt  -- The ON condition makes it a CROSS JOIN  This is where the inner join can really be seen as the cross join with results not matching the condition removed  Here none of the resulting rows are removed  Using INNER JOIN to get the result of a LEFT OUTER JOIN also requires tricks  It s omitted  LEFT OUTER JOIN LEFT JOIN results in rows as The General Idea CROSS JOIN  SELECT     FROM citizen         c   LEFT JOIN postalcode p ON 1   1 --  lt  -- The ON condition makes it a CROSS JOIN  LEFT JOIN results in rows as The General Idea INNER JOIN  SELECT     FROM citizen         c   LEFT JOIN postalcode p ON c postal   p postal  WHERE p postal IS NOT NULL --  lt  -- removed the row where there s no mathcing result from postalcode   The troubles with the Venn diagram An image internet search on  quot sql join cross inner outer quot  will show a multitude of Venn diagrams  I used to have a printed copy of one on my desk  But there are issues with the representation  Venn diagram are excellent for set theory  where an element can be in one or both sets  But for databases  an element in one  quot set quot  seem  to me  to be a row in a table  and therefore not also present in any other tables  There is no such thing as one row present in multiple tables  A row is unique to the table  Self joins are a corner case where each element is in fact the same in both sets  But it s still not free of any of the issues below  The set A represents the set on the left  the citizen table  and the set B is the set on the right  the postalcode table  in below discussion  CROSS JOIN Every element in both sets are matched with every element in the other set  meaning we need A amount of every B elements and B amount of every A elements to properly represent this Cartesian product  Set theory isn t made for multiple identical elements in a set  so I find Venn diagrams to properly represent it impractical impossible  It doesn t seem that UNION fits at all  The rows are distinct  The UNION is 7 rows in total  But they re incompatible for a common SQL results set  And this is not how a CROSS JOIN works at all   Trying to represent it like this     but now it just looks like an INTERSECTION  which it s certainly not  Furthermore there s no element in the INTERSECTION that is actually in any of the two distinct sets  However  it looks very much like the searchable results similar to this   For reference one searchable result for CROSS JOINs can be seen at Tutorialgateway  The INTERSECTION  just like this one  is empty  INNER JOIN The value of an element depends on the JOIN condition  It s possible to represent this under the condition that every row becomes unique to that condition  Meaning id x is only true for one row  Once a row in table A  citizen  matches multiple rows in table B  postalcode  under the JOIN condition  the result has the same problems as the CROSS JOIN  The row needs to be represented multiple times  and the set theory isn t really made for that  Under the condition of uniqueness  the diagram could work though  but keep in mind that the JOIN condition determines the placement of an element in the diagram  Looking only at the values of the JOIN condition with the rest of the row just along for the ride   This representation falls completely apart when using an INNER JOIN with a ON 1   1 condition making it into a CROSS JOIN  With a self-JOIN  the rows are in fact idential elements in both tables  but representing the tables as both A and B isn t very suitable  For example a common self-JOIN condition that makes an element in A to be matching a different element in B is ON A parent   B child  making the match from A to B on seperate elements  From the examples that would be a SQL like this  SELECT     FROM citizen c1   JOIN citizen c2 ON c1 id   c2 leader   Meaning Smith is the leader of both Green and Jensen  OUTER JOIN Again the troubles begin when one row has multiple matches to rows in the other table  This is further complicated because the OUTER JOIN can be though of as to match the empty set  But in set theory the union of any set C and an empty set  is always just C  The empty set adds nothing  The representation of this LEFT OUTER JOIN is usually just showing all of A to illustrate that rows in A are selected regardless of whether there is a match or not from B  The  quot matching elements quot  however has the same problems as the illustration above  They depend on the condition  And the empty set seems to have wandered over to A   WHERE clause - making sense Finding all rows from a CROSS JOIN with Smith and postalcode on the Moon  SELECT     FROM citizen          c  CROSS JOIN postalcode  p  WHERE c name    Smith     AND p area    Moon     Now the Venn diagram isn t used to reflect the JOIN  It s used only for the WHERE clause     and that makes sense  When INTERSECT and UNION makes sense INTERSECT As explained an INNER JOIN is not really an INTERSECT  However INTERSECTs can be used on results of seperate queries  Here a Venn diagram makes sense  because the elements from the seperate queries are in fact rows that either belonging to just one of the results or both  Intersect will obviously only return results where the row is present in both queries  This SQL will result in the same row as the one above WHERE  and the Venn diagram will also be the same  SELECT     FROM citizen          c  CROSS JOIN postalcode  p  WHERE c name    Smith  INTERSECT SELECT     FROM citizen          c  CROSS JOIN postalcode  p  WHERE p area    Moon    UNION An OUTER JOIN is not a UNION  However UNION work under the same conditions as INTERSECT  resulting in a return of all results combining both SELECTs  SELECT     FROM citizen          c  CROSS JOIN postalcode  p  WHERE c name    Smith  UNION SELECT     FROM citizen          c  CROSS JOIN postalcode  p  WHERE p area    Moon    which is equivalent to  SELECT     FROM citizen          c  CROSS JOIN postalcode  p  WHERE c name    Smith     OR p area    Moon      and gives the result   Also here a Venn diagram makes sense   When it doesn t apply An important note is that these only work when the structure of the results from the two SELECT s are the same  enabling a comparison or union  The results of these two will not enable that  SELECT     FROM citizen  WHERE name    Smith   SELECT     FROM postalcode  WHERE area    Moon      trying to combine the results with UNION gives a ORA-01790  expression must have same datatype as corresponding expression   For further interest read Say NO to Venn Diagrams When Explaining JOINs and sql joins as venn diagram  Both also cover EXCEPT

User · Answer

The precise algorithm for INNER JOIN  LEFT RIGHT OUTER JOIN are as following     Take each row from the first table  a Consider all rows from second table beside it   a  b i   Evaluate the ON     clause against each pair  ON  a  b i      true false    When the condition evaluates to true  return that combined row   a  b i    When reach end of second table without any match  and this is an Outer Join then return a  virtual  pair using Null for all columns of other table   a  Null  for LEFT outer join or  Null  b  for RIGHT outer join  This is to ensure all rows of first table exists in final results     Note  the condition specified in ON clause could be anything  it is not required to use Primary Keys  and you don t need to always refer to Columns from both tables   For example        ON T1 title   T2 title AND T1 version  lt  T2 version      see this post as a sample usage  Select only rows with max value on a column      ON T1 y IS NULL     ON 1   0  just as sample          Note  Left Join   Left Outer Join  Right Join   Right Outer Join

User · Answer

Assuming you re joining on columns with no duplicates  which is a very common case    An inner join of A and B gives the result of A intersect B  i e  the inner part of a Venn diagram intersection  An outer join of A and B gives the results of A union B  i e  the outer parts of a Venn diagram union    Examples  Suppose you have two tables  with a single column each  and data as follows   A    B -    - 1    3 2    4 3    5 4    6   Note that  1 2  are unique to A   3 4  are common  and  5 6  are unique to B   Inner join  An inner join using either of the equivalent queries gives the intersection of the two tables  i e  the two rows they have in common   select   from a INNER JOIN b on a a   b b  select a    b    from a b where a a   b b   a   b -- -- 3   3 4   4   Left outer join  A left outer join will give all rows in A  plus any common rows in B   select   from a LEFT OUTER JOIN b on a a   b b  select a    b    from a b where a a   b b      a    b -- ----- 1   null 2   null 3      3 4      4   Right outer join  A right outer join will give all rows in B  plus any common rows in A   select   from a RIGHT OUTER JOIN b on a a   b b  select a    b    from a b where a a      b b   a       b ----- ---- 3       3 4       4 null    5 null    6   Full outer join  A full outer join will give you the union of A and B  i e  all the rows in A and all the rows in B  If something in A doesn t have a corresponding datum in B  then the B portion is null  and vice versa   select   from a FULL OUTER JOIN b on a a   b b    a      b ----- -----    1   null    2   null    3      3    4      4 null      6 null      5

User · Answer

A inner join only shows rows if there is a matching record on the other  right  side of the join   A  left  outer join shows rows for each record on the left hand side  even if there are no matching rows on the other  right  side of the join  If there is no matching row  the columns for the other  right  side would show NULLs

User · Answer

There are a lot of good answers here with very accurate relational algebra examples   Here is a very simplified answer that might be helpful for amateur or novice coders with SQL coding dilemmas  Basically  more often than not  JOIN queries boil down to two cases  For a SELECT of a subset of A data   use INNER JOIN when the related B data you are looking for MUST exist per database design  use LEFT JOIN when the related B data you are looking for MIGHT or MIGHT NOT exist per database design

User · Answer

INNER JOIN most typical join for two or more tables  It returns data match on both table ON primarykey and forignkey relation  OUTER JOIN is same as INNER JOIN  but it also include NULL data on ResultSet    LEFT JOIN   INNER JOIN   Unmatched data of left table with Null match on right table  RIGHT JOIN   INNER JOIN   Unmatched data of right table with Null match on left table  FULL JOIN   INNER JOIN   Unmatched data on both right and left tables with Null matches   Self join is not a keyword in SQL  when a table references data in itself knows as self join  Using INNER JOIN and OUTER JOIN we can write self join queries    For example   SELECT    FROM   tablea a         INNER JOIN tableb b                 ON a primary key   b foreign key         INNER JOIN tablec c                 ON b primary key   c foreign key

User · Answer

There are major mainly 2 types of JOINs in SQL   INNER and OUTER   Examples Suppose you have two tables  with a single column each  and data as follows  A    B -    - 1    3 2    4 3    5 4    6 7 8  Note that  1 2 7 8  are unique to A   3 4  are common  and  5 6  are unique to B      INNER  JOIN   The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies  This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i e value of the common field will be the same   select   from a INNER JOIN b on a a   b b  select a    b    from a b where a a   b b   Result  a   b -- -- 3   3 4   4     LEFT  OUTER  JOIN   This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of the join  The rows for which there is no matching row on the right side  the result-set will contain null  LEFT JOIN is also known as LEFT OUTER JOIN   select   from a LEFT OUTER JOIN b on a a   b b  select a    b    from a b where a a   b b      Result  a    b -- ----- 1   null 2   null 3      3 4      4 7   null 8   null     RIGHT  OUTER  JOIN  Returns all records from the right table  and the matched records from the left table   select   from a RIGHT OUTER JOIN b on a a   b b  select a    b    from a b where a a      b b   Result  a       b ----- ---- 3       3 4       4 null    5 null    6     FULL  OUTER  JOIN  FULL JOIN creates the result-set by combining the result of both LEFT JOIN and RIGHT JOIN  The result-set will contain all the rows from both the tables  The rows for which there is no matching  the result-set will contain NULL values     select   from a FULL OUTER JOIN b on a a   b b   Result   a      b ----- -----    1   null    2   null    3      3    4      4 null      6 null      5    7   null    8   null

User · Answer

The difference between inner join and outer join is as follow    Inner join is a join that combined tables based on matching tuples  whereas outer join is a join that combined table based on both matched and unmatched tuple  Inner join merges matched row from two table in where unmatched row are omitted  whereas outer join merges rows from two tables and unmatched rows fill with null value  Inner join is like an intersection operation  whereas outer join is like an union operation  Inner join is two types  whereas outer join are three types  outer join is faster than inner join

User · Answer

Inner joins require that a record with a related ID exist in the joined table   Outer joins will return records for the left side even if nothing exists for the right side   For instance  you have an Orders and an OrderDetails table  They are related by an  OrderID    Orders   OrderID CustomerName   OrderDetails   OrderDetailID OrderID ProductName Qty Price   The request  SELECT Orders OrderID  Orders CustomerName   FROM Orders   INNER JOIN OrderDetails     ON Orders OrderID   OrderDetails OrderID   will only return Orders that also have something in the OrderDetails table   If you change it to OUTER LEFT JOIN  SELECT Orders OrderID  Orders CustomerName   FROM Orders    LEFT JOIN OrderDetails     ON Orders OrderID   OrderDetails OrderID   then it will return records from the Orders table even if they have no OrderDetails records   You can use this to find Orders that do not have any OrderDetails indicating a possible orphaned order by adding a where clause like WHERE OrderDetails OrderID IS NULL

User · Answer

INNER JOIN requires there is at least a match in comparing the two tables  For example  table A and table B which implies A   B  A intersection B    LEFT OUTER JOIN and LEFT JOIN are the same  It gives all the records matching in both tables and all possibilities of the left table   Similarly  RIGHT OUTER JOIN and RIGHT JOIN are the same  It gives all the records matching in both tables and all possibilities of the right table   FULL JOIN is the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN without duplication

User · Answer

In Simple Terms   1 INNER JOIN OR EQUI JOIN   Returns the resultset that matches only the condition in both the tables   2 OUTER JOIN   Returns the resultset of all the values from both the tables even if there is condition match or not    3 LEFT JOIN   Returns the resultset of all the values from left table and only rows that match the condition in right table   4 RIGHT JOIN   Returns the resultset of all the values from right table and only rows that match the condition in left table   5 FULL JOIN   Full Join and Full outer Join are same

User · Answer

Joins are more easily explained with an example   To simulate persons and emails stored in separate tables  Table A and Table B are joined by Table A id   Table B name id Inner Join  Only matched ids  rows are shown  Outer Joins  Matched ids and not matched rows for Table A are shown   Matched ids and not matched rows for Table B are shown   Matched ids and not matched rows from both Tables are shown  Note  Full outer join is not available on MySQL

User · Answer

In simple words   An inner join retrieve the matched rows only   Whereas an outer join retrieve the matched rows from one table and all rows in other table     the result depends on which one you are using    Left  Matched rows in the right table and all rows in the left table Right  Matched rows in the left table and all rows in the right table or  Full  All rows in all tables  It doesn t matter if there is a match or not

User · Answer

Consider below 2 tables   EMP  empid   name    dept id salary 1       Rob     1       100 2       Mark    1       300 3       John    2       100 4       Mary    2       300 5       Bill    3       700 6       Jose    6       400   Department  deptid  name 1       IT 2       Accounts 3       Security 4       HR 5       R amp D   Inner Join   Mostly written as just JOIN in sql queries  It returns only the matching records between the tables   Find out all employees and their department names   Select a empid  a name  b name as dept name FROM emp a JOIN department b ON a dept id   b deptid    empid   name    dept name 1       Rob     IT 2       Mark    IT 3       John    Accounts 4       Mary    Accounts 5       Bill    Security   As you see above  Jose is not printed from EMP in the output as it s dept id 6 does not find a match in the Department table   Similarly  HR and R amp D rows are not printed from Department table as they didn t find a match in the Emp table    So  INNER JOIN or just JOIN  returns only matching rows   LEFT JOIN    This returns all records from the LEFT table and only matching records from the RIGHT table   Select a empid  a name  b name as dept name FROM emp a LEFT JOIN department b ON a dept id   b deptid    empid   name    dept name 1       Rob     IT 2       Mark    IT 3       John    Accounts 4       Mary    Accounts 5       Bill    Security 6       Jose       So  if you observe the above output  all records from the LEFT table Emp  are printed with just matching records from RIGHT table   HR and R amp D rows are not printed from Department table as they didn t find a match in the Emp table on dept id   So  LEFT JOIN returns ALL rows from Left table and only matching rows from RIGHT table    Can also check DEMO here

User · Answer

In simple words   Inner join - gt  Take ONLY common records from parent and child tables WHERE primary key of Parent table matches Foreign key in Child table  Left join - gt  pseudo code 1 Take All records from left Table 2 for each record in right table         if Records from left  amp  right table matching on primary  amp  foreign key          use their values as it is as result of join at the right side for 2nd table        else          put value NULL values in that particular record as result of join at the right side for 2nd table             Right join   Exactly opposite of left join   Put name of table in LEFT JOIN at right side in Right join   you get same output as LEFT JOIN  Outer join   Show all records in Both tables No matter what  If records in Left table are not matching to right table based on Primary   Forieign key   use NULL value as result of join   Example    Lets assume now for 2 tables 1 employees    2 phone numbers employees employees   id   name   phone numbers employees   id   phone num   emp id     Here   employees table is Master table   phone numbers employees is child table it contains emp id as foreign key which connects employee id so its child table   Inner joins Take the records of 2 tables ONLY IF Primary key of employees table its id  matches Foreign key of Child table phone numbers employees emp id   So query would be   SELECT e id   e name   p phone num FROM employees AS e INNER JOIN phone numbers employees AS p ON e id   p emp id   Here take only matching rows on primary key   foreign key as explained above Here non matching rows on primary key   foreign key are skipped as result of join  Left joins   Left join retains all rows of the left table  regardless of whether there is a row that matches on the right table  SELECT e id   e name   p phone num FROM employees AS e LEFT JOIN phone numbers employees AS p ON e id   p emp id   Outer joins   SELECT e id   e name   p phone num FROM employees AS e OUTER JOIN phone numbers employees AS p ON e id   p emp id   Diagramatically it looks like

User · Answer

The Venn diagrams don t really do it for me  They don t show any distinction between a cross join and an inner join  for example  or more generally show any distinction between different types of join predicate or provide a framework for reasoning about how they will operate  There is no substitute for understanding the logical processing and it is relatively straightforward to grasp anyway   Imagine a cross join  Evaluate the on clause against all rows from step 1 keeping those where the predicate evaluates to true  For outer joins only  add back in any outer rows that were lost in step 2    NB  In practice the query optimiser may find more efficient ways of executing the query than the purely logical description above but the final result must be the same  I ll start off with an animated version of a full outer join  Further explanation follows    Explanation Source Tables  First start with a CROSS JOIN  AKA Cartesian Product   This does not have an ON clause and simply returns every combination of rows from the two tables  SELECT A Colour  B Colour FROM A CROSS JOIN B  Inner and Outer joins have an  quot ON quot  clause predicate   Inner Join  Evaluate the condition in the  quot ON quot  clause for all rows in the cross join result  If true return the joined row  Otherwise discard it  Left Outer Join  Same as inner join then for any rows in the left table that did not match anything output these with NULL values for the right table columns  Right Outer Join  Same as inner join then for any rows in the right table that did not  match anything output these with NULL values for the left table columns  Full Outer Join  Same as inner join then preserve left non matched rows as in left outer join and right non matching rows as per right outer join   Some examples SELECT A Colour  B Colour FROM A INNER JOIN B ON A Colour   B Colour The above is the classic equi join   Animated Version  SELECT A Colour  B Colour FROM A INNER JOIN B ON A Colour NOT IN   Green   Blue   The inner join condition need not necessarily be an equality condition and it need not reference columns from both  or even either  of the tables  Evaluating A Colour NOT IN   Green   Blue   on each row of the cross join returns   SELECT A Colour  B Colour FROM A INNER JOIN B ON 1  1 The join condition evaluates to true for all rows in the cross join result so this is just the same as a cross join  I won t repeat the picture of the 16 rows again  SELECT A Colour  B Colour FROM A LEFT OUTER JOIN B ON A Colour   B Colour Outer Joins are logically evaluated in the same way as inner joins except that if a row from the left table  for a left join  does not join with any rows from the right hand table at all it is preserved in the result with NULL values for the right hand columns   SELECT A Colour  B Colour FROM A LEFT OUTER JOIN B ON A Colour   B Colour WHERE B Colour IS NULL This simply restricts the previous result to only return the rows where B Colour IS NULL  In this particular case these will be the rows that were preserved as they had no match in the right hand table and the query returns the single red row not matched in table B  This is known as an anti semi join  It is important to select a column for the IS NULL test that is either not nullable or for which the join condition ensures that any NULL values will be excluded in order for this pattern to work correctly and avoid just bringing back rows which happen to have a NULL value for that column in addition to the un matched rows   SELECT A Colour  B Colour FROM A RIGHT OUTER JOIN B ON A Colour   B Colour Right outer joins act similarly to left outer joins except they preserve non matching rows from the right table and null extend the left hand columns   SELECT A Colour  B Colour FROM A FULL OUTER JOIN B ON A Colour   B Colour Full outer joins combine the behaviour of left and right joins and preserve the non matching rows from both the left and the right tables   SELECT A Colour  B Colour FROM A FULL OUTER JOIN B ON 1   0 No rows in the cross join match the 1 0 predicate  All rows from both sides are preserved using normal outer join rules with NULL in the columns from the table on the other side   SELECT COALESCE A Colour  B Colour  AS Colour FROM A FULL OUTER JOIN B ON 1   0 With a minor amend to the preceding query one could simulate a UNION ALL of the two tables   SELECT A Colour  B Colour FROM A LEFT OUTER JOIN B ON A Colour   B Colour WHERE B Colour    Green  Note that the WHERE clause  if present  logically runs after the join  One common error is to perform a left outer join and then include a WHERE clause with a condition on the right table that ends up excluding the non matching rows  The above ends up performing the outer join         And then the  quot Where quot  clause runs  NULL   Green  does not evaluate to true so the row preserved by the outer join ends up discarded  along with the blue one  effectively converting the join back to an inner one   If the intention was to include only rows from B where Colour is Green and all rows from A regardless the correct syntax would be SELECT A Colour  B Colour FROM A LEFT OUTER JOIN B ON A Colour   B Colour AND B Colour    Green   SQL Fiddle See these examples run live at SQLFiddle com

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

Examples related to sql

Examples related to database

Examples related to join

Examples related to inner-join

Examples related to outer-join