SQL Part 3: Electric JOINS

by Algirdas Grajauskas

JOINS:

When joining tables one needs to imagine how joins work in programs such as Tableau or Alteryx, the principle is the same e.g. imagine venn diagrams but you can't see them visually unlike in Tableau or Alteryx.

  • Inner Join:

Look at two different data sources and join them on things that are common ( things you've specified)

In SNOWFLAKE the logic is to choose FROM which table and to INNER JOIN on to which e.g. "Superstore_Orders" and "Superstore_People", keep in mind when doing this you also need to specify prefixes for these tables in my case I chose s for Orders and p for People. When joining onto columns ON

This type of query returns Total Sales for each Sales manager (Person) and matching on Region.

Another example

Is counting distinct orders from a certain table in this case wold be "Superstore_Returns" and to return the amount of orders returned.

Pay attention that we need to specify which table we are counting the "Order ID" from, in this case because we also used "WHERE" as a filter to return just the amount of returned orders, and there also has to be the prefix on "Order ID" to specify which table we are counting from, if we do not do that we would get

This type of error, because "Order ID" exists in both tables.

  • Full Join:

Full joins take everything from both sides and combine them together, therefore there's no need to use filtering techniques such as "WHERE". It can simply be written out

This way it is possible to return both total orders and returned orders by just counting them. One thing do not forget to keep adding prefixes to each thing that can exists in both or multiple tables.

  • RIGHT and LEFT inner joins :

The syntax would be "LEFT JOIN" or "RIGHT JOIN". The way to visualize is for LEFT JOIN the full circle of A gets filled while for RIGHT JOIN the colours are inversed.

They are basically almost the same as inner join, except they also take all the information from either right or left table and joins it with information that matches between the two tables.

One more thing with snowflake it is possible to join multiple times in on query for example:

In this case everything from the returns was joined with orders, and only the people (salesperson) was joined with inner join onto the orders table. This way we can have the sales people for each region and also find out how many orders were returned the table for this SQL query looked like this:

Keep in mind, that if a column that exists in both tables in this case it was "Region" and it was not specified which one the query should use to aggregate everything, it kept dropping the previous error of "ambiguous name". Therefore, before every "Region" there needs to be a prefix s.

  • Additional examples:

By using "IS NULL" function it is possible to create tables which would actually exclude things which match.

To be able to achieve this, essentially we use either a LEFT or RIGHT join

SELECT "Things that you want to select"

FROM TableA A

LEFT JOIN TableB B

ON A.Key = B.Key

WHERE B.Key IS NULL

The WHERE query helps us to filter information that we do not need, by doing this we would only return everything that did not match between A and B. For Alteryx, users the best way to visualize this is to think of the join tool and how you have the L, J, R. Where J would represent what was joined together between two tables and the L and R would be what was left over. Therefore using the IS NULL essentially just returns the "Left over" bits from table A in this case.

Thank you for reading. :)

Avatar

Algirdas Grajauskas

Fri 29 Jul 2022

Thu 28 Jul 2022

Wed 27 Jul 2022