New SQL Beginner's Tutorial: JOIN Me and Together We Will Rule the Universe

SQL Beginner's Tutorial: JOIN Me and Together We Will Rule the Universe
dbzfan4awhile (57) dans sql-forbeginners • hier
JOIN_Me_Meme.jpg

Hi all of my Jedi Knights and Sith Lords,

Today you will JOIN me in my adventure to teach you more SQL (with Attitude) to continue on towards our final goal of creating a game. Yes, again, you heard that right!! We are making a game! It's going to go very slowly, b/c (let's face it) I also have a day job and a life outside of Steemit. Maybe not much of a life outside of Steemit, but it's there and my wife appreciates that I pay attention to her.

[Us]

So, today's Lesson: Using the JOIN statement.

Using the JOIN Statement in Your Queries

Of the many statements and keywords that you will use in querying data, the JOIN is the one that will be the most confusing in actual use but the most effective in filtering unwanted data out of the results. The JOIN keyword essentially tells us to take a pair of tables and join them together. It's like if you had a pair of Excel-based spreadsheets and needed to include both worksheets as a single result-set. In Excel you'd have to find some common linkage or know what corresponds to what. Then you'd have to actually do some Excel magic (which I'm not going into any further as this is not an Excel Tutorial).

In SQL, the JOIN will effectively do this for you as long as you know what the Foreign Keys are between the tables. When I say Foreign Keys, I mean those data columns that will be the same in both tables. As a quick example, let's say I have school children and backpacks that I've set up in a database (don't ask why... maybe the teacher wants to store this information for some organizational reason). The child has been given a unique ID that they are set up under in a table called "SchoolChildren"; and the backpacks are given a name-tag that is attached to it... in the database, the "BackPack" table has a column called "backpack_id" which directly corresponds to the child's ID. The child's ID would be a Primary Key and backpack's "backpack_id" would be a Foreign Key. Let's say now that child "10101" has 5 backpacks (1 for homework, 1 for Show-and-Tell, 1 for Snacks, 1 for extra clothes, and 1 for toys... it could happen). How do you tie back all 5 backpacks? by giving each one the same "10101" value for its "backpack_id".

BackPack

Certain SQL-based systems can query to pull in all of the data regardless of commonalities; however, Microsoft SQL Server Management Studio (which is the interface I use) does not allow an OUTER JOIN which does this. What we can do, though, is pull in all of the data from 1 table regardless of commonality with the other (or vice versa). To do so, we would use the LEFT OUTER JOIN and RIGHT OUTER JOIN to specify which table to use as the base table.