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.
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.
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.