Skip to content
Fix Code Error

What is the difference between “INNER JOIN” and “OUTER JOIN”?

March 13, 2021 by Code Error
Posted By: cdv

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

Solution

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
Answered By: Mark Harrison

Related Articles

  • SQL query return data from multiple tables
  • sql query to find priority jobs
  • SQL find sum of entries by date including previous date
  • data.table vs dplyr: can one do something well the…
  • Pandas Merging 101
  • Union of multiple Database queries with same parameters
  • SQL JOIN and different types of JOINs
  • How can I determine whether a 2D Point is within a Polygon?
  • generate days from date range
  • insert tables in dataframe with years from 2000 to…
  • Extract from Union type where discriminator is also a Union
  • How do I keep only the first map and when the game…
  • "Large data" workflows using pandas
  • Fix top buttons on scroll of list below
  • Set the local state using useEffect on Redux prop change
  • Is it possible to apply CSS to half of a character?
  • Database development mistakes made by application developers
  • SQLGrammarException:error executing work ORA-01722:…
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • How do i arrange images inside a div?
  • Java ElasticSearch None of the configured nodes are…
  • Active tab issue on page load HTML
  • INNER JOIN vs LEFT JOIN performance in SQL Server
  • How to use 2 columns as "key" to get MAX value of…
  • The opposite of Intersect()
  • 3 column layout HTML/CSS
  • How to edit `cell[i]` in data grid view C# win form…
  • The definitive guide to form-based website authentication
  • Why doesn't the height of a container element…
  • TypeScript metadata reflection references other…
  • What is your most productive shortcut with Vim?
  • Using IS NULL or IS NOT NULL on join conditions -…
  • How should a model be structured in MVC?
  • Sort a list in C# by custom index and sameness
  • Polymer 1.0 Trying to make a splitter which works…
  • How does PHP 'foreach' actually work?
  • useEffect Error: Minified React error #321 (GTM…
  • Combine two tables that have no common fields
  • BigQuery: user metadata described by start date and…
  • SQL left join vs multiple tables on FROM line?
  • How to change the color of vaadin-select-text-field…
  • Adobe XD to responsive html
  • After a little scroll, the sticky navbar just is not…
  • Rewrite left outer join involving multiple tables…
  • Change column type in pandas
  • Left Outer Join using + sign in Oracle 11g
  • Getting the closest string match
  • Design DFA accepting binary strings divisible by a…
  • What are the undocumented features and limitations…
  • Ukkonen's suffix tree algorithm in plain English
  • How to make a stable two column layout in HTML/CSS
  • OpenCL - Approximation of Pi via Monte Carlo…
  • reformat a pandas dataframe
  • How to prevent scrolling the whole page?
  • Sorting 1 million 8-decimal-digit numbers with 1 MB of RAM
  • SQL Server: Query fast, but slow from procedure
  • How to return rows from left table not found in right table?
  • VueJS components ref is undefined at all stages
  • What's the difference between eval, exec, and compile?
  • Java Array, Finding Duplicates
  • Avoid multiple copy of data when composing objects…
  • Addition of two queries - SQL/HANA
  • How do JavaScript closures work?
  • How to select rows based on two columns creating an…
  • Jquery fadeToggle Trouble
  • Reference — What does this symbol mean in PHP?
  • In CSS Flexbox, why are there no "justify-items" and…
  • SQL / Teradata - How can I get the most recent…
  • Smart way to truncate long strings
  • When should I use cross apply over inner join?
  • Why does C++ code for testing the Collatz conjecture…
  • Pandas datetimeindex intersection with more than two indexes
  • T-SQL How to create tables dynamically in stored procedures?
  • How to get the in HTML tables to fit content, and…
  • What is the correct way to split an aurelia app into…
  • Is there some way to test my grammar which refer to…
  • How to use html template with vue.js
  • Good MapReduce examples
  • Can't stratify output in a customized way
  • What's the best way of scraping data from a website?
  • CSS grid wrapping
  • Conditional WHERE clauses with TVP parameter
  • Deviantart style image gallery CSS
  • Memcached vs. Redis?
  • Vuetify Tabs change active when scroll
  • Best way to find the intersection of multiple sets?
  • Having trouble with my nav bar/header, It used to…
  • SQL Server CTE and recursion example
  • SQL: parse the first, middle and last name from a…
  • Split cell value that based on a delimiter and…
  • How to find column names for all tables in all…
  • Know relationships between all the tables of…
  • What is difference between INNER join and OUTER join
  • "Thinking in AngularJS" if I have a jQuery background?
  • r squared based on columns from 2 dataframes
  • Oracle manage historical info and new entries
  • How to execute UNION without sorting? (SQL)
  • Show Rows That Are Different Between Two Tables - MS Access
  • .Net Core 3.1 Entity Framework Slow Query Problem
  • How can I manually compile a svelte component down…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

How do I shutdown, restart, or log off Windows via a bat file?

Next Post:

SQL SELECT WHERE field contains words

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error