SQL Lesson 10. Joining tables (INNER JOIN)

The most powerful feature of the SQL language is the ability to combine different tables in the RAM of the DBMS during query execution. Joins are very often used for data analysis. As a rule, the data is in different tables, which allows them to be stored more efficiently (since information is not duplicated), simplifies data processing, and allows the database to be scaled (it is possible to add new tables with additional information).

The database tables used in DBMS MS Access are relational tables, that is, all tables can be linked to each other by common fields.

1. Creating a combination of tables (JOINS)

Combining tables is a very simple procedure. It is necessary to specify all the tables that will be included in the combination and "explain" to the DBMS how they will be related to each other. The connection is made using the word WHERE, for example:

Run SQLSELECT DISTINCT Seller_name, Product 
FROM Sellers, Sumproduct 
WHERE Sellers.City = Sumproduct.City

By combining the two tables, we were able to see which products each seller sells. Let's take a closer look at the request code, as it is slightly different from a normal request. The SELECT statement begins by specifying the columns we want to display, but these fields are in different tables, the FROM clause contains two tables that we want to join in the SELECT statement, the tables are joined using the WHERE word, which specifies the columns to join. You must specify the full name of the field (Table.Field), because the field City is in both tables.

2. Internal combination (INNER JOIN)

In the previous example, we used the word WHERE to combine tables, which performs a check based on the equivalence of two tables. A combination of this type is also called an "inner combination". There is also another way of combining tables that clearly indicates the type of combination. Consider the following example:

Run SQLSELECT DISTINCT Seller_name, Product 
FROM Sellers 
INNER JOIN Sumproduct 
ON Sellers.City = Sumproduct.City

In this query, instead of WHERE, we used the construction INNER JOIN ... ON ..., which gave a similar result. Although the combination with the WHERE clause is shorter, it is still better to use INNER JOIN because it is more flexible, as will be explained in more detail in the following sections.