> For the complete documentation index, see [llms.txt](https://otd.gitbook.io/book/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://otd.gitbook.io/book/module-8/table-operations/joins.md).

# Joins

What if we want to select data from multiple tables?

### KEY TERMS

* **join:** the process of combining two tables
* **dot notation:** the syntax we use to find specific characteristics of an object, like a table

One of the largest benefits of SQL is using short and simple queries to narrow down information from a large aggregate of data. For better organizational management, data is often stored in multiple tables. For example, in our previous section our data was reflected in a table *specific* to individual dogs. However, say we also have a more general table on dog breed characteristics. Below is our **breeds** table.

| Breed           | Origin Country | Size   |
| --------------- | -------------- | ------ |
| French Bulldog  | France         | Medium |
| Yellow Lab      | Canada         | Large  |
| Beagle          | England        | Medium |
| Border Collie   | England        | Medium |
| German Shepherd | Germany        | Large  |
| Chihuahua       | Mexico         | Small  |

We can combine the **breeds** table with the **dogs** table from the previous section using a **join**. 

To accomplish joining at a base level, all we have to do is select from both of our tables, and separating them with a comma. However, we have to change up our notation a little bit. Within our statement, we must use what is called **dot notation**. Dot notation in this case is essentially using a "dot" to specify where our column is coming from. We can't simply say where breed = breed. We must note that we are saying that the breed column from **dogs** must equal the breed column from **breeds** table. Below is an example of how we specify that.

```sql
select * from dogs, breeds where dogs.breed = breed.breed;
```

While the join operation above is a very basic example, sometimes we want even more specificity in the way we join tables. We may be trying to combine dozens of tables at the same time, and therefore, a more directed operation may be required

#### Left Join

An left-join is a table join operation where all of the rows of the left side table are returned, as well as the matching rows from the rightwards table(s). For example, say we only wanted the columns from the **breeds** table in our final table, but has another table **more dogs** with a list of every dog. The resulting table from a left join would be much longer (as it may contain thousands of dog breeds) but the only relevant information to us would be the *Breed, Origin Country,* and *Size* columns from **more dogs**. The syntax for a left join is shown below.

```sql
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
```

While this formula is a little bit more complicated than the examples we've seen before, we can walk through it line by line.

1. We use dot notation to access specific columns
2. table1 is the table we select columns from
3. table2's columns are the columns that make up the final table
4. `ON` is very similar to the `WHERE` syntax we've seen before. In specified joins, we typically use ON as a way to define our join condition. We only will copy the matching columns that are contained in table2's original columns. The rows for which there is no matching row on the right side, the set of results will contain *null*

Similarly, we can do something very similar with a **right join**. A right join follows almost the exact same pattern, but we join to the right table instead. There are a few specific cases where the difference between a left join and a right join are important to decipher, but for the purposes of this course, we can think of them very similarly. While which type of join you choose is up to you, it is fairly common for data scientists to mainly use **left join**.

#### Full Join

Full Joins are useful when we want to have a resulting table with *every* column of every table we've selected from. Essentially, a full join is the combination of a left join and a right join. You might be asking what happens when we have rows that do not match amongst the tables. Say one of the tables we full joined with **breeds** had a couple cats in it, while containing a country of origin and size value. The resulting table would simply include a *null* value for that specific cat's breed in the *Breed* column of our final table. The syntax for a full join is shown below.

```sql
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
```

Stepping through line by line again, we can interpret the following:

* We use dot notation to select specific columns we want in our full join
* We specify the table we are trying to get data from
* We call a **full join** operation. This means we want every column from our select statement in our final table
* We specify our join condition

These three table operations are the main types of joins that we use in SQL. These operations let us both append data to a specified table, as well as aggregate all of our information in a final table.