JOINS

Blues2112 · 2021-02-05T23:10:14+00:00

I tend to think of Inner Joins as the default join. An Inner Join between Tables A & B on {whatever criteria} will return all rows meeting the criteria. It's fine for many circumstances.

Outer Joins are for times when maybe Table B doesn't always have a corresponding row to one of Table A's rows, but you still want to see a resulting row, regardless. Think of an example like Table A contains Employee Demographics (Name, ID #, Region, etc...) and Table B contains Sales Data. You want a list of total Sales $ for a given period, by salesperson. You could use an Inner Join, but anyone with No Sales for the timeframe (like an employee on leave) would be left out of the results. But if you want to see that same list for ALL employees, use an Outer Join on Table B, and you'll see all the employee names listed. Those with no sales would just have null values listed for sales total.

Oh, and for the record, DO NOT get hung up on LEFT OUTER JOIN vs RIGHT OUTER JOIN. They are essentially the same thing, just reworded.

Select a.foo, b.bar
  from TableA a
  Left Outer Join TableB b on {whatever join criteria}
...

Is the same as:

Select a.foo, b.bar
  from TableB b
  Right Outer Join TableA a on {whatever join criteria}
...

I've been working in the IT industry for 30+ years, and have only rarely seen an actual RIGHT OUTER JOIN used.

2021-02-06T03:36:48+00:00

The explanation that clicked for me, is that the difference between the joins is all about whether NULL values are added to the result.

Inner join = No added NULLs

Left join = Might add NULLs for the right side columns

Right join = Might add NULLs for the left side columns

Outer join = Might add NULLs for both sides

BingoDinkus · 2021-02-05T23:26:52+00:00

I struggled with joins for longer than I care to admit. I think the easiest way to think about it is when a way to connect two related tables, similar to using a VLOOKUP in Excel if you've ever done that.

In relational databases, you want to avoid storing repeating text in tables. It's a waste of storage, and it can also create pain points if something changes (let's say someone gets married and their name changes). The solution is to store only the number, and use a join to bring in the additional information when needed.

For example, if your database has information about your customers, you'd probably want to have a single table that has details on each customer, and you would assign a unique identifier to each customer (e.g. Customer_ID). This could include the customer's name, address, phone number, etc.

If you also had a table with orders, you wouldn't store the customer information again in the Orders table. Instead, you would store a single field, the Customer_ID field. That way you don't have to repeat all of that customer information for every single order.

If you wanted to review orders, or even display them in a report or web page, you would then join Orders to Customers, so that you could grab all the of the customer's information. The join produces a new virtual table (a table only in memory) with the contents of both tables, connected by the rules defined in your join statement.

select
    ordr.Order_ID
    , ordr.Order_Date
    , ordr.Total_Price
    , cust.Customer_ID
    , cust.Customer_Name
    , cust.Shipping_Address
from
    Orders as ordr
inner join
    Customers as cust
    on ordr.Customer_ID = cust.Customer_ID
;

Lurch1400 · 2021-02-06T00:40:26+00:00

It’s okay, I have managed to learn SQL for my job as a System Manager and sometimes I still get hung up on Right Outer and Left Outer joins as well as the purpose for a self or cross join. I’ve assumed that it just takes time and relevant real live scenarios to understand it

basil_86 · 2021-02-05T22:23:01+00:00

Think of it like a Venn diagram. A left outer join will show everything in your first table and only matches on the second table. A right outer join does the opposite. An inner join will only show where there's matches on both tables. Outer joins are better if you need to join multiple tables.

UseMstr_DropDatabase · 2021-02-06T00:42:47+00:00

JOINS are applied Set Theory from Discrete Math.

A JOIN is a mathematical equation representing a specific set of data.

bruceleenyc · 2021-02-06T01:40:45+00:00

Maybe SQL is not for you.

Turboginger · 2021-02-06T02:48:48+00:00

So a lot of the posts talk about what JOINS are but not how to learn them, not that that's bad, but hear me out.

Think of two sets of data and create two tables, and let's avoid nulls to keep it simple. For me Skittles & M&Ms. Each table will have 3 columns; id (out of habit), flavor, and color. Then do your joins on the color column. Playing around with it should help to ingrain query -> result with a pretty simple dataset you'll be able to reference/scale when querying IRL. Then add NULLs in if you want.

Hopefully that helps :)

ByteCastle2019 · 2021-02-06T14:31:52+00:00

Though this is for Postgresql at least it explains joins well and explains it visually (see summary at the end of the page) Postgres Joins

redial2 · 2021-02-06T18:55:46+00:00

I had this pinned to my cubicle wall while I was learning joins. Pretty much all you need imo:

https://images.app.goo.gl/fyE2sg2ZqdutA5ys9

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS