SQL Tutorial: Joining Data in SQL | Intro

---
Hi, my name is Chester Ismay and I'll be your instructor for this course on Joining Data in PostgreSQL.

As the name suggests, the focus of this course is using SQL to join two or more database tables together into a single table...an essential skill for data scientists.

Specifically, you'll learn how to use an INNER JOIN to match values appearing in two separate tables. You'll use OUTER JOINs to bring in all matching values as well as retain records that don't have a match. You'll use CROSS JOINs to create all combinations of fields in multiple tables.

You'll investigate set operations for combining multiple tables into a single table, learn about semi-joins and anti-joins, and use nested queries (also known as subqueries) to solve tough database problems. Each chapter will close with exercises designed to help you check your understanding of the topics. By the end, you'll have the skills necessary to face any database with confidence!

See you in the course!

In this chapter, you'll learn about the INNER JOIN, which along with LEFT JOIN are probably the two most common JOINs. You'll see diagrams throughout this course that are designed to help you understand the mechanics of the different joins. Let's begin with a diagram showing the layout of some data and then how an INNER JOIN can be applied to that data.

In the videos in this chapter and the next, we'll often work with two tables named `left` and `right`. You can see that matching values of the `id` field are colored with the same color. The `id` field is known as a KEY field since it can be used to reference one table to another. Both the `left` and `right` tables also have another field named `val`. This will be useful in helping you see specifically which records and values are included in each join.

An INNER JOIN only includes records in which the key is in both tables.

You can see here that the `id` field matches for values of 1 and 4 only. With inner joins we look for matches in the right table corresponding to all entries in the key field in the left table.

So the focus here shifts to only those records with a match in terms of the `id` field. The records not of interest to INNER JOIN have been faded.

Here's a resulting single table from the INNER JOIN clause that gives the `val` field from the `right` table with records corresponding to only those with `id` value of 1 or 4, which are colored as yellow and purple. Now that you have a sense for how INNER JOIN works, let's try an example in SQL.

The `prime_ministers` table is one of the tables in the `leaders` database. It is displayed here. Note the countries that are included. Suppose you were interested in determining nations that have both a prime minister and a president AND putting the results into a single table. Next you'll see the `presidents` table.

How did I display all of the `prime_ministers` table in the previous slide? Recall the use of `SELECT` and `FROM` clauses as is shown for the `presidents` table here.

Which countries appear in both tables? With small tables like these, it is easy to notice that Egypt, Portugal, Vietnam, and Haiti appear in both tables. For larger tables, it isn't as simple as just picking these countries out visually. So what does the syntax look like for SQL to get the results of countries with a prime minister and a president from these two tables into one?

The syntax for completing an INNER JOIN from the `prime_ministers` table to the `presidents` table based on a key field of `country` is shown.

Note the use of aliases for `prime_ministers` as `p1` and `presidents` as `p2`. This helps to simplify your code, especially with longer table names like `prime_ministers` and `presidents`. A `SELECT` statement is used to select specific fields from the two tables. In this case, since `country` exists in both tables, we must write `p1` and the period to avoid a SQL error. Next we list the table on the left of the inner join after `FROM` and then we list the table on the right after `INNER JOIN`. Lastly, we specify the keys in the two tables that we would like to match on.

You'll now practice applying an inner join to two tables and to three tables. Let's get to it!

#SQLTutorial #DataCamp #Joining #data