What Is a LEFT OUTER JOIN in SQL? An Explanation With 4 Examples

Dương Trạng

8 tháng trước

At this time’s article will talk about the LEFT OUTER JOIN in SQL. We’ll undergo a number of examples of LEFT OUTER JOIN utilization and examine it with INNER JOIN.

A JOIN is an SQL characteristic that lets you mix information from two or extra tables. The character of relational databases makes JOIN one of the generally used options in SQL. Why? In observe, you’ll very seldom have all of the required information in a single desk. Even probably the most primitive databases encompass at the least two tables.

There are numerous various kinds of JOINs. We’ll give attention to LEFT OUTER JOIN right here. To get a complete recap of various kinds of JOINs, attempt our interactive SQL JOINs course. Apart from the fundamentals, it explains becoming a member of a number of tables and utilizing self- and non-equi joins. You’ll full over 90 hands-on workout routines – all primarily based on real-world examples!

Let’s introduce LEFT OUTER JOIN; then we’ll make it extra fascinating with sensible examples. In the event you want a refresher on some ideas as we go alongside, bear in mind our SQL JOIN Cheat Sheet.

What Is a LEFT OUTER JOIN?

The most typical JOIN is INNER JOIN. It’s a be a part of sort that returns solely the matching rows from each joined tables. There are different JOIN sorts that may return rows from a joined desk even when the row has no matching row within the different desk. All these JOINs are known as outer joins.

A LEFT JOIN is a sort of outer be a part of that outputs all rows from the left desk and the matching rows from the correct desk.

What’s the Distinction Between LEFT OUTER JOIN And LEFT JOIN?

Quick reply: There’s no distinction!

Lengthy reply: The total title of any such be a part of actually is LEFT OUTER JOIN. You’ve most likely seen SQL code examples the place the tables are joined solely with LEFT JOIN. It is because SQL accepts each LEFT OUTER JOIN and LEFT JOIN.

As LEFT JOIN is shorter, it’s used extra usually. Whenever you see it, it merely means LEFT OUTER JOIN. The ‘Outer’ is implied, as there’s no different left be a part of than a left outer be a part of.

LEFT [OUTER] JOIN Syntax

The syntax of LEFT JOIN follows the usual JOIN syntax:

Reference the primary desk in FROM.
Use the LEFT JOIN key phrase to reference the second desk.
Use the ON key phrase to specify the becoming a member of situation.

In different phrases, the syntax is:

SELECT … FROM table_1 LEFT JOIN table_2 ON table_1.column = table_2.column;

So how does LEFT OUTER JOIN work? Within the generic instance above, the question will reference table_1 and left be a part of it with table_2. It’ll first return all the rows from table_1, irrespective of the becoming a member of situation. Why? As a result of that is the character of LEFT JOIN – it returns all of the rows from the left (i.e. first) desk.

Then the question will take a look at the becoming a member of situation – on this case, the place a worth in column from table_2 matches a worth from column from table_1. The LEFT JOIN will return solely values from the correct (i.e. second desk, or table_2) desk the place the becoming a member of situation is matched. When no such values are in the correct desk, the returned values shall be NULL. You possibly can see a visible instance of this in our article on how the LEFT JOIN works.

When the tables are joined, it goes with out saying that you would be able to select any column from each tables in SELECT.

Now that you understand the syntax, the one factor left is to place it into observe.

4 Examples of LEFT OUTER JOIN

The Dataset for Examples 1-3

First, let’s introduce the dataset.

The primary desk, departments, has the next information:

iddepartment_name 1Accounting 2Sales 5Compliance

You will get the question for creating the desk right here.

The second desk is workers, and you’ll create it utilizing this question. The desk has the next information:

idfirst_namelast_nameemaildepartment_id 1DellaHinchshawdhinchshaw@firm.com1 2RoanaAndraudrandraud@firm.com2 3NettleDrewellndrewell@firm.com3 4CoralieLandreclandre@firm.com3 5FredericaKetchasidefketchaside@firm.com1 6FeneliaGuisotfguisot@firm.com1 7MarysaPortchmportch@firm.comNULL 8HarlenDrakardhdrakard@firm.com2 9TiffieHauchthauch@firm.comNULL 10LuraGravellslgravells@firm.com1 11FaeLagdenflagden@firm.com4 12ChuchoBearcbear@firm.com4 13TracieBellisontbellison@firm.com2 14CharitaMissencmissen@firm.com1 15BearShoulderbshoulder@firm.com1

Two workers have a NULL worth within the column department_id. These are new worker information that haven’t been up to date with the division but.

Instance 1: Discover All Workers and Their Departments

Let’s use the above tables to record all the workers and their departments.

That is an article about LEFT OUTER JOIN, so there’s no shock: we’ll use precisely that be a part of to unravel this drawback. Right here’s the question:

SELECT e.id, e.first_name, e.last_name, d.department_name FROM workers e LEFT JOIN departments d ON e.department_id = d.id ORDER BY e.id;

The consequence information we’d like is the workers’ ID numbers, names, and departments. That’s why these columns are in SELECT.

To get all these columns, we have to entry information from each tables. Let’s see how that is achieved.

First, the desk workers is referenced in FROM. Why this desk and never the opposite one? Keep in mind: Which desk is referenced first issues in LEFT JOIN. Why? As a result of that desk is the left desk, and LEFT JOIN will return all of the rows from that desk it doesn’t matter what.

We reference the desk workers first as a result of we have to record all the workers from that desk. Then we reference the desk departments. We be a part of each tables on the situation that the column department_id from the desk workers is similar because the column id from the desk departments. These two columns are shared dimensions (the first key and international key) between these two tables, so that they’re perfect to be used within the be a part of situation.

To have extra readable output, we order the outcomes by worker ID:

idfirst_namelast_namedepartment_name 1DellaHinchshawAccounting 2RoanaAndraudSales 3NettleDrewellNULL 4CoralieLandreNULL 5FredericaKetchasideAccounting 6FeneliaGuisotAccounting 7MarysaPortchNULL 8HarlenDrakardSales 9TiffieHauchNULL 10LuraGravellsAccounting 11FaeLagdenNULL 12ChuchoBearNULL 13TracieBellisonSales 14CharitaMissenAccounting 15BearShoulderAccounting

The output lists all the workers – all 15 of them. It additionally exhibits their departments, that are ‘Accounting’ and ‘Gross sales’. You’ll discover that some workers have NULL values within the department_name column. Two of them we talked about earlier: new workers with out an up to date division ID within the desk. These had NULL values within the preliminary workers desk, and are marked in blue.

There are, nonetheless, different workers – marked in inexperienced – with NULL values. That is the results of the LEFT JOIN. All these workers have a division ID of 4, however the desk departments doesn’t comprise this worth. It appears that evidently the desk departments could be calling for an replace, too. A extremely shabby database for such a good fictive firm!

Keep in mind: The values from the left desk not present in the correct desk shall be proven as NULL.

What If We Used an INNER JOIN?

Let’s write the above question once more, this time with an INNER JOIN (often abbreviated to only JOIN) as a substitute of a LEFT JOIN:

SELECT e.id, e.first_name, e.last_name, d.department_name FROM departments d JOIN workers e ON e.department_id = d.id ORDER BY e.id;

The syntax is strictly the identical; we simply used a special be a part of sort. Let’s see what the consequence shall be:

idfirst_namelast_namedepartment_name 1DellaHinchshawAccounting 2RoanaAndraudSales 5FredericaKetchasideAccounting 6FeneliaGuisotAccounting 8HarlenDrakardSales 10LuraGravellsAccounting 13TracieBellisonSales 14CharitaMissenAccounting 15BearShoulderAccounting

Some workers are lacking; there are solely 9 right here. Nearer inspection exhibits that the workers with IDs 3, 4, 7, 9, 11, and 12 should not included within the consequence. Why? In the event you return, you’ll see that the lacking workers are these with NULLs in department_name within the LEFT OUTER JOIN output.

Keep in mind, INNER JOIN will return solely matching rows from each tables – in different phrases, solely these workers who’ve a division ID present in each tables.

So on this scenario, the place we wished all workers and their departments, the LEFT JOIN is the correct selection. Now we additionally see the workers with out the division, and we will inform our database wants updating.

Instance 2: Record All Departments and Their Workers

To get the specified consequence, we now have to change the order of the tables in LEFT JOIN.

Right here’s the question:

SELECT d.id, d.department_name, e.first_name, e.last_name FROM departments d LEFT JOIN workers e ON d.id = e.department_id ORDER BY d.id;

We tweak the order of the columns in SELECT to current the output higher. A lot of the columns are the identical as earlier than. This time, we’re choosing the division ID from the desk departments, not the worker ID.

Now, the desk departments is our left desk. It is because we wish to present all departments from that desk, whether or not or not they seem within the desk workers – our proper desk.

The ON situation stays the identical; we solely reversed the order of the columns. It actually doesn’t matter; it may have remained in the identical order. That is only for aesthetic causes, because it’s simpler to learn the situation when it follows the order of the tables in LEFT JOIN.

Additionally, we type the output by division ID.

Right here’s the consequence:

iddepartment_namefirst_namelast_name 1AccountingDellaHinchshaw 1AccountingFredericaKetchaside 1AccountingFeneliaGuisot 1AccountingLuraGravells 1AccountingCharitaMissen 1AccountingBearShoulder 2SalesHarlenDrakard 2SalesTracieBellison 2SalesRoanaAndraud 5ComplianceNULLNULL

This output exhibits solely 9 workers, in comparison with 15 within the earlier instance. All workers with NULL values within the final instance’s output don’t seem on this output.

The reason being, once more, LEFT JOIN. As we made departments our left desk, it listed all of the departments and their workers. All the opposite workers should not right here. Why? Both they’ve division ID 4, which doesn’t seem within the desk departments, or they’re new workers (bear in mind them?) with NULL as a division ID.

The one NULLs on this output seem within the final row. There’s the division ‘Compliance’, which doesn’t have any workers allotted to it. In different phrases, there aren’t any workers with the worth 5 within the column department_id of the workers desk.

Keep in mind: The order of the tables in LEFT JOIN issues!

What If We Used an INNER JOIN?

Let’s change the question and use an INNER JOIN:

SELECT d.id, d.department_name, e.first_name, e.last_name FROM departments d INNER JOIN workers e ON d.id = e.department_id ORDER BY d.id;

The output is mainly the identical as once we interior joined Instance 1. The one distinction is that there’s a division ID as a substitute of an worker ID. Apart from that, it’s the identical, so we received’t spend time analyzing it.

Instance 3: Record All Departments and the Variety of Workers in Every

LEFT JOIN, like all different joins, is commonly used with SQL’s combination features. It will turn out to be useful right here.

Take a look at this question:

SELECT d.department_name, COUNT(e.id) AS number_of_employees FROM departments d LEFT JOIN workers e ON d.id = e.department_id GROUP BY d.department_name;

Let’s begin explaining from the FROM clause. Since we wish to record all the present departments, we first discuss with the desk departments in FROM. Why? As a result of there are some workers and not using a division, and we don’t need them in our consequence. As we noticed, there’s additionally a division with out workers; we would like this division within the output regardless.

Then follows the desk workers after LEFT JOIN. The tables are joined on the identical situation as within the earlier instance.

To get the proper output, we have to record the division title in SELECT. We additionally want to make use of the COUNT(e.id) combination operate to depend the variety of worker IDs.

Why can’t we use COUNT(*)? As a result of it counts all of the rows, together with NULL values. This may skew our outcomes. The compliance division has zero workers, which might have been proven as NULL when joined. COUNT(*) would have counted this NULL as one, which might not be correct.

The COUNT(e.id) possibility ignores NULL values and can depend solely workers whose ID just isn’t NULL. That is extra of an combination operate matter than a LEFT JOIN matter, so we received’t go into additional particulars.

Nonetheless, this can be very vital to know when to make use of the varied COUNT() choices. You possibly can discuss with our article detailing all of the completely different incarnations and makes use of of the COUNT() operate for extra data.

Again to our code. After counting and becoming a member of, the output is grouped by the division title. All that can consequence within the record of departments with the variety of workers in every division:

department_namenumber_of_employees Accounting6 Compliance0 Sales3

The consequence exhibits there are, in complete, 9 workers throughout the corporate departments: six in Accounting, zero in Compliance, and three in Gross sales.

What If We Used INNER JOIN?

Now, let’s do the identical question however with INNER JOIN.

SELECT d.department_name, COUNT(e.id) AS number_of_employees FROM departments d JOIN workers e ON d.id = e.department_id GROUP BY d.department_name;

Will the consequence be completely different? Let’s see.

department_namenumber_of_employees Accounting6 Sales3

The consequence, once more, exhibits there are 9 workers throughout numerous departments. What is clearly lacking is that the corporate has a Compliance division that’s not proven on this consequence.

If we had used INNER JOIN, we might have concluded that there are solely two departments within the firm. LEFT JOIN was, once more, the correct selection since there could also be (at the least briefly) a division with out workers.

Dataset for Instance 4

We’ll use three tables on this instance. The primary one is artists; it incorporates the next information about musical artists:

idartist_name 1Isaac Hayes 2Paul Simon 3Stevie Surprise 4George Benson

Create the desk utilizing the question right here.

The subsequent desk is albums:

idalbum_titleyear_releasedartist_idgrammy_category_id 1Caribou1974NULL2 2Still Loopy After All These Years197521 3Fulfillingness’ First Finale197431 4Stranger to Stranger20162NULL 5The Wall1979NULL2 6Songs within the Key of Life197631 7Black Moses19711NULL 8Innervisions197431 9Shaft197112 10Let’s Dance1983NULL2

And right here’s the question to create this desk.

The question for the third desk is right here. The desk is known as grammy_award. It’s an inventory of Grammy award classes. It has solely two classes: the most effective album winner and the most effective album nominee.

idgrammy_category 1Album of the 12 months Winner 2Album of the 12 months Nominee

Instance 4: Record All of the Artists, Their Albums, and the Grammy Award Class

LEFT JOIN will also be used to hitch greater than two tables, and we’ll see how within the question beneath:

SELECT artist_name, album_title, grammy_category FROM artists ar LEFT JOIN albums al ON ar.id = al.artist_id LEFT JOIN grammy_award ga ON al.grammy_category_id = ga.id;

First, we listed all of the required columns to point out the artist’s title, the album title, the yr of launch, and the Grammy Award class.

We see from the introduced tables that there are albums with out artist data. Our catalog is incomplete, so the most secure method to record all of the out there artists is to LEFT JOIN tables and think about the desk artists because the left desk. It’s left joined with the desk albums on the artist ID.

To fetch the info about Grammys, we should someway be a part of the third desk. How is that this achieved? Easy: write the LEFT JOIN command once more and reference the desk grammy_award after it. It will LEFT JOIN albums with grammy_award. The tables are joined on the Grammy class ID.

Let’s see the output:

artist_namealbum_titlegrammy_category Isaac HayesShaftAlbum of the 12 months Nominee Isaac HayesBlack MosesNULL Paul SimonStranger to StrangerNULL Paul SimonStill Loopy After All These YearsAlbum of the 12 months Winner Stevie WonderInnervisionsAlbum of the 12 months Winner Stevie WonderSongs within the Key of LifeAlbum of the 12 months Winner Stevie WonderFulfillingness’ First FinaleAlbum of the 12 months Winner George BensonNULLNULL

The consequence exhibits all of the artists, their albums, and whether or not the album was a nominee or a winner for the Album of the 12 months Award. It additionally exhibits the albums that had been neither Grammy nominees nor winners.

What If We Used INNER JOIN?

Right here’s the identical code as above, with JOIN as a substitute of LEFT JOIN:

SELECT artist_name, album_title, grammy_category FROM artists ar JOIN albums al ON ar.id = al.artist_id JOIN grammy_award ga ON al.grammy_category_id = ga.id;

Let’s see what the code returns:

artist_namealbum_titlegrammy_category Isaac HayesShaftAlbum of the 12 months Nominee Paul SimonStill Loopy After All These YearsAlbum of the 12 months Winner Stevie WonderInnervisionsAlbum of the 12 months Winner Stevie WonderSongs within the Key of LifeAlbum of the 12 months Winner Stevie WonderFulfillingness’ First FinaleAlbum of the 12 months Winner

This result’s incomplete. It’s lacking one artist: George Benson. It’s additionally lacking the albums with none Grammy class. Since we weren’t making an attempt to record solely the albums with a Grammy class, the LEFT JOIN was the correct selection.

Listed here are some extra explanations on find out how to LEFT JOIN a number of tables.

Newcomers’ Nook: Some Tips about Writing JOINs

Right here’s an outline of the commonest errors inexperienced persons make when utilizing the LEFT OUTER JOIN in SQL.

We already coated them within the examples, nevertheless it won’t be that apparent to a much less skilled eye. So let’s put them into phrases.

Selecting the Right Be part of

We in contrast all of the LEFT JOIN examples with the INNER JOIN variations. As you noticed, the output modifications considerably. That’s why it’s good to rigorously select which be a part of you’ll use.

One of the best tip is to consider these two be a part of definitions. So, for those who want solely matching information from two tables, then you definitely want INNER JOIN. In all different instances, LEFT JOIN will most likely be the proper selection.

Deciding Which Desk Is the Left One

When you select to make use of the LEFT JOIN, how have you learnt which desk ought to be the left desk? To start with, the left one is the desk that comes instantly after FROM. The correct one is the one which comes after LEFT JOIN.

However how do you determine which is which? You must accurately perceive the issue you’re fixing. Search for cues within the wording and logic. In the event you want all workers, then the desk with the workers would be the left one. In different phrases, for those who assume considered one of your tables must be proven unchanged, that’s your left desk.

Be Cautious With Mixture Capabilities

Even for those who make a mistake and select the incorrect be a part of, it’s very possible that you just’ll catch it within the output for those who don’t combination the info. In the event you’re accustomed to your information, you’ll see some rows lacking or no NULLs the place you anticipated them to be.

Nonetheless, be further cautious when utilizing combination features. Because the output shall be aggregated, will probably be far more troublesome to see the error from the output – except you understand all of your information aggregation outcomes by coronary heart, which is very unlikely. You wouldn’t want SQL in that case, would you?

That is very true when utilizing COUNT(). As you noticed in considered one of our examples, COUNT(*) can provide you completely different outcomes from COUNT(column_name). It’s extraordinarily vital that you understand what you wish to obtain and the way COUNT() works.

Keep in mind: COUNT(column_name) ignores NULL values, whereas COUNT(*) doesn’t!

So when working with combination features, take a look at your question with INNER JOIN – if in case you have the chance – and see if it returns completely different outcomes. If it doesn’t, then it doesn’t matter which be a part of you utilize. If it does, return to your drawback definition and see which be a part of logic higher fits your online business logic.

Cascading LEFT JOINs

That is noticed when becoming a member of greater than two tables. In the event you select to LEFT JOIN the primary two tables, you often must LEFT JOIN all the opposite tables.

You noticed that in our final instance, as we used two LEFT JOINs. Even when we used a LEFT JOIN first after which an INNER JOIN, we wouldn’t get the record of all of the artists and the opposite information. Together with INNER JOIN on this chain of joins will give the identical consequence as utilizing INNER JOIN as a substitute of every LEFT JOIN.

This isn’t all the time the case. However it’s fairly a protected wager to comply with this rule of thumb: for those who want one LEFT JOIN, you want all LEFT JOINs.

Trying For Extra LEFT JOIN Observe?

This text gave you a style of what a LEFT JOIN is and find out how to handle its occasional trickiness. We additionally practiced writing code as you bought accustomed to the LEFT JOIN syntax.

To grasp all of the nuances of LEFT OUTER JOIN in SQL, we suggest extra observe. Three examples should not sufficient! To actually construct on what you discovered right here, we suggest our SQL JOINs course. Other than theoretical foundations, you’ll additionally get loads of hands-on observe with real-world situations.

Listed here are some extra concepts on find out how to observe SQL JOINs. SQL JOINs interview questions are additionally a useful useful resource for training supplies. Glad studying!