Data Mastery: SQL — Left & Right Outer Join There are so many exciting projects out there in the Data World. Artificial Intelligence, Machine Learning, Neural Nets, Blockchain, and more are sweeping the technology industry. In order to get to the cutting-edge stuff, first and foremost, data needs to be stored, evaluated, and tested. The best place to do that is SQL (or a library that operates with SQL-like commands, ). see my article on Python’s Pandas library This series , will teach you the essential subjects. These are not exhaustive tutorials. Instead they are focused preparation guides — with brevity and efficiency in mind. It is meant for: Data Mastery: SQL Software Engineers who want to analyze their creation’s data Product Managers, Marketers, and others who want to be data-driven Beginning Data Scientists, Data Engineers, Data Analysts, or Business Intelligence Developers preparing for interviews See my resource list of the books I used to prepare for my big interview Each article will contain a brief technical explanation of the subject, an example question, and an answer. Follow up articles will contain challenging questions so you can evaluate your learning. This series does not come with accompanying data sets. The advantage to this is when you are on the drawing board, whether in an interview or project design, you do not have test data to play with. You have to think abstract. Left & Right Outer Join These joins are similar to Inner Joins except for one thing: they return all results that appear in one table and whatever matches up from the other. Recall our daily_user_score table from earlier in the series: date       | userid    | sessionid | score
------------------------------------------
2018–09–01 | 983489272 | 125       | 112
2018–09–01 | 234342423 | 34        | 112
2018–09–01 | 567584329 | 207       | 618
2018–09–02 | 983489272 | 126       | 410
2018–09–02 | 983489272 | 127       | 339 And remember our table user_revenue: userid    | revenue
--------------------
983489272 | 100
234342423 | 33
567584329 | 57
305852827 | 8
867736361 | 29 The best way to understand Outer Joins is to see them in action. Say we want to see for all users how much they paid and their scores on 2018–09–01. This is how we query the database: SELECT r.userid, r.revenue, d.score
FROM user_revenue AS r
LEFT OUTER JOIN daily_user_score AS d
ON d.userid = r.userid
AND d.date = ‘2018–09–01’; The results of this query are: userid    | revenue | score
---------------------------
983489272 | 100     | 112
234342423 | 33      | 112
567584329 | 57      | 618
305852827 | 8       | NULL
867736361 | 29      | NULL Notice that the users who do not have a corresponding record in the daily_user_score table have a NULL value for their score — this is the Outer Join. A LEFT JOIN will include all rows from the table after FROM. A RIGHT JOIN will include all rows from the table after JOIN. Try it yourself Write a query that for each row in daily_user_score include the revenue made off that particular user. Answer SELECT d.date, d.userid, d.sessionid, d.score, r.revenue
FROM user_revenue AS r
RIGHT OUTER JOIN daily_user_score AS d
ON d.userid = r.userid; OR SELECT d.date, d.userid, d.sessionid, d.score, r.revenue
FROM daily_user_score AS d
LEFT OUTER JOIN user_revenue AS r
ON d.userid = r.userid; These queries return: date       | userid    | sessionid | score | revenue
----------------------------------------------------
2018–09–01 | 983489272 | 125       | 112   | 100
2018–09–01 | 234342423 | 34        | 112   | 33
2018–09–01 | 567584329 | 207       | 618   | 57
2018–09–02 | 983489272 | 126       | 410   | 100
2018–09–02 | 983489272 | 127       | 339   | 100 Thanks for reading! If you have questions feel free to comment & I will try to get back to you. Connect with me on Instagram @ljglass Connect with me on LinkedIn Check out my essentials list on Amazon Search for me using my nametag on Instagram!

Amazon

Instagram

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

Data Mastery: SQL — Left & Right Outer Join

Too Long; Didn't Read

Companies Mentioned

Lauren Glass

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

Data Mastery: SQL — Left & Right Outer Join

Too Long; Didn't Read

Companies Mentioned

Lauren Glass

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES