Last Updated:

JOIN in MySQL: INNER, LEFT, RIGHT, FULL, CROSS with Examples

What is JOIN?

JOIN is an operation to combine two rows into one. These rows usually come from two different tables, but this is not necessary.

Before we look at how to write JOIN-s, let's see what table joining looks like.

 

Take for example a system that stores information about users and their addresses.

 

A table that stores user information might look like this:

id | name | email | age —-+—————+———————+—— 1 | John Smith | johnsmith@gmail.com | 25 2 | Jane Doe | janedoe@Gmail.com | 28 3 | Xavier Wills | xavier@wills.io | 3 ... (7 rows)

And the table with addresses can be as follows:

id | street | city | state | user_id —-+——————-+—————+——-+——— 1 | 1234 Main Street | Oklahoma City | OK | 1 2 | 4444 Broadway Ave | Oklahoma City | OK | 2 3 | 5678 Party Ln | Tulsa | OK | 3 (3 rows)

To get information about both the user and his address, we can write two different requests. But ideally, you can write one request and get all the necessary information in one answer.

 

That's what merge operations are for!

 

A little later, we'll look at how to compose such queries, but for now, let's take a look at how you might see the result of joining tables:

id | name | email | age | id | street | city | state | user_id —-+—————+———————+——+—-+——————-+—————+——-+——— 1 | John Smith | johnsmith@gmail.com | 25 | 1 | 1234 Main Street | Oklahoma City | OK | 1 2 | Jane Doe | janedoe@Gmail.com | 28 | 2 | 4444 Broadway Ave | Oklahoma City | OK | 2 3 | Xavier Wills | xavier@wills.io | 35 | 3 | 5678 Party Ln | Tulsa | OK | 3 (3 rows)

We see all our users at once with their addresses.

But merge operations allow you not just to display such combined information. They have another important function: with their help, you can get filtered results.

For example, if we want to send real paper emails to all users living in Oklahoma, we can combine tables and filter the results by the city column.

Now that you've figured out what merge operations are for at all, let's start writing queries!

Basic principles of connection

By using a join, you can retrieve data from two or more tables based on the logical relationships between them. Joins allow you to specify how SQL Server should use data from one table to select rows from another table.

A join defines how two tables in a query are linked as follows:

 
  • for each table, the columns used in the join are specified. A typical join condition specifies a foreign key from one table and a related key from another table;
  • specifies a logical operator (for example, = or <>,) to compare column values.

Connections are expressed logically by using the following Transact-SQL syntax:

  • INNER JOIN
  • LEFT [ OUTER ] JOIN
  • RIGHT [ OUTER ] JOIN
  • FULL [ OUTER ] JOIN
  • CROSS JOIN

You can specify internal connections in the FROM and WHERE clauses. External joins and cross-joins can only be specified in the FROM clause. Join conditions are combined with WHERE and HAVING search terms to control the rows selected from the underlying tables referenced by the FROM clause.

The fact that the connection conditions are specified in the FROM clause helps to separate them from the search terms that can be specified in the WHERE clause. It is recommended that you specify merging in this way. The following is a simplified connection syntax using the ISO from clause:

 

FROM first_table < join_type > second_table [ ON ( join_condition ) ]

 

join_type indicates whether the connection is internal, external, or cross-joined. join_condition defines a predicate that will be evaluated for each pair of strings to connect. The following is an example of a FROM clause with a specified connection:

FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor ON ( ProductVendor.BusinessEntityID = Vendor.BusinessEntityID )

The following is a simple SELECT statement that uses this connection:

SELECT ProductID, Purchasing.Vendor.BusinessEntityID, Name FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor ON (Purchasing.ProductVendor.BusinessEntityID = Purchasing.Vendor.BusinessEntityID) WHERE StandardPrice > $10 AND Name LIKE N’F%’ GO

The SELECT statement returns the product name and vendor information for all parts combinations shipped by companies with names in the letter F and a product value greater than $10.

If a single query contains references to multiple tables, all column references must be unambiguous. In the previous example, both the ProductVendor table and the Vendor table contain a column named BusinessEntityID. Column names that match in two or more tables referenced by the query must be qualified with the table name. All references to the Vendor column in this example are complete.

If the column name is not duplicated in the two or more tables specified in the query, references to it do not need to be qualified by the name of the table. This is shown in the previous example. Such a SELECT clause is sometimes difficult to understand because there is nothing in it that points to the tables from which the columns are taken. A query is much easier to read if all columns are specified with the names of the corresponding tables. The query will be even easier to read if you use table aliases, especially when the names of the tables themselves must be qualified by the names of the database and the owner. The following is the same example, but to make it easier to read, table aliases are used to refine column names.

SELECT pv. ProductID, v.BusinessEntityID, v.Name FROM Purchasing.ProductVendor AS pv INNER JOIN Purchasing.Vendor AS v ON (pv. BusinessEntityID = v.BusinessEntityID) WHERE StandardPrice > $10 AND Name LIKE N’F%’;
 

In the previous example, the connection condition is specified in the FROM clause, which is the recommended method. In the following query, the same connection condition is specified in the WHERE clause:

SELECT pv. ProductID, v.BusinessEntityID, v.Name FROM Purchasing.ProductVendor AS pv, Purchasing.Vendor AS v WHERE pv. BusinessEntityID=v.BusinessEntityID AND StandardPrice > $10 AND Name LIKE N’F%’;

A SELECT list for a join can reference all columns in joined tables or any subset of those columns. A SELECT list does not have to contain columns from each table in the join. For example, in a three-table join, there can be only one table as a link between one table and a third table, and the selection list does not have to reference the columns in the middle table. This is the so-called anti-semi-connection.

Although the equality operator (=) is typically used for comparison in connection conditions, you can specify other comparison operators or relational operators, as well as other predicates. For more information, see Comparison Operators (Transact-SQL) and WHERE (Transact-SQL).

When processing connections in SQL Server, the query optimizer selects the most efficient processing method possible. This includes choosing the most efficient type of physical join, the order in which the tables will be joined, and even using types of logical join operations that cannot be directly expressed using Transact-SQL syntax, such as half-join and anti-half-join. Many different optimizations can be used when physically executing different connections, so they cannot be reliably predicted. For more information about half-meshes and anti-half-ways, see the Showplan Logical and Physical Operator Reference.

Columns used in a join condition do not have to have the same name or the same data type. However, if the data types do not match, they must be compatible or allow implicit conversion in SQL Server. If the data types do not implicitly transform, the connection conditions must explicitly transform these data types by using the CAST function. For more information about implied and explicit transformations, see Data Type Conversion (Database Engine).

Most queries that use a connection can be overwritten by using nested queries and vice versa. For more information about nested queries, see Nested Queries.

Note

Tables cannot be joined directly by ntext, text, or image columns. However, you can connect tables by ntext, text, or image columns indirectly by using SUBSTRING.
For example, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) performs an internal join of two tables on the first 20 characters of text columns in tables t1 and t2.


Another way to compare the ntext and text columns from the two tables is to compare the length of the columns with the WHERE clause, for example: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)

Understanding Nested Loop Joins

If one connection input is small (less than ten rows) and the other input is relatively large and indexed across the columns being joined, the nested loop index join is the fastest join operation because it will require the fewest comparison and I/O operations.

Joining nested loops, also called nested iteration, uses one input of the join as the external input table (in the graphical execution diagram, it is the upper input) and the second as the internal (lower) input table. The outer loop uses the external input table line by line. In the inner loop, the inner input table is scanned for each outer row and the matching rows are displayed.

In the simplest case, a search looks at the entire table or index; this is called a simplified join of nested loops. If an index is used in the search, the search is called an index join of nested loops. If an index is created as part of a query plan (and destroyed after the query is complete), it is called a temporary index join of nested loops. All of these options are considered by the query optimizer.

Joining nested loops is especially effective when the external inputs are relatively small and the internal inputs are large and pre-indexed. In many small transactions that work with small rowsets, the index join of nested loops is superior to both merge and hash joins. However, in large requests, joining nested loops is often not the best option.

If the OPTIMIZED attribute of the nested loop join operator is set to True, it means that optimized nested loop joins (or batch sorting) are used to reduce I/O when the internal table is large, regardless of whether it is being processed in parallel. This optimization in this execution plan may not be very obvious when analyzing the plan, if the sorting itself is performed as a hidden operation. However, if you examine the plan XML for the OPTIMIZED attribute, you may find that joining nested loops might attempt to change the order of the input strings to improve I/O performance.

Understanding Merge Connections

If the two connection inputs are large enough but sorted by the columns being joined (for example, if they were retrieved by looking at sorted indexes), the fastest join operation would be a merge join. If both connection inputs are large and have similar sizes, the pre-sort merge join and the hash connection have roughly the same performance. However, hash join operations are often faster if the two inputs are significantly different in size.

A merge join requires sorting both input sets into merge columns that are defined by the equality (ON) clauses of the merge predicate. The query optimizer typically looks at the index if one exists for the corresponding set of columns, or sets a sort statement under the merge connection. In rare cases, there may be multiple equality clauses, but merge columns are taken from only some of the available equality clauses.

Because each set of input data is sorted, the Merge Join statement gets a row from each set of input data and compares it. For example, for internal join operations, strings are returned if they are equal. If they are not equal, a string with a lower value is ignored, and another row is taken from this input dataset. This process is repeated until all rows have been processed.

 

A merge join operation can be either a regular merge operation or a many-to-many operation. A many-to-many merge join uses a temporary table to store rows. If there are duplicate values from each input dataset, one of the sets will need to be reset to the beginning of the duplicates as each duplicate from the other dataset is processed.

If there is a residual predicate, all rows that satisfy the merge predicate define a residual predicate, and only those rows that match it are returned.

A merge join is a very quick operation, but it can be resource-intensive if sorting operations are required. However, if the data volume is large and the necessary data can be retrieved from existing B-tree indexes with pre-sorting performed, the merge join is the fastest connection algorithm available.

Illustrative example of using the JOIN operator

There are two tables tags(id, name), tag_links(tag_id, post_id) Make a request:

SELECT tags.name FROM tags LEFT JOIN tag_links ON tag_links.tag_id = tags.id WHERE tag_links.post_id = 1

This will select/get tag names for the entry with post_id = 1.

You can decrypt the request like this:


We take name from tags, join the tag_links table with values that satisfy tag_links.tag_id = tags.id, where tag_links.post_id = 1

General JOIN Statement Syntax

SELECT <collections> FROM [<pending conditions="">] JOIN ON
The example above words can be described as follows: "Select <collections> from a table concatenated in equality with the table according to the following condition". In the condition, a column from the first table equal to the column from the second. Values from these columns will be compared if they are equal, then the result of equal rows will be returned.

Roughly speaking, we attach a table to a table . Which columns of one table to connect with the columns of another and by what condition is described in the block (in most DBMSs, only the equality condition is supported, so it is assumed that the JOIN operator merges the specified columns of two tables only under the condition of equality).

Attachment conditions for tables

  • LEFT — selects all records from the first (left) table
  • RIGHT — selects all records from the three (right) table
  • FULL — selects all records from both tables (left and right)

LEFT and RIGHT select all records from a given table and corresponding records from another table.

Operator Description

select field [,… n] from Table1 {inner | {left | right | full} outer | cross } join Table2 on

In most DBMS, when specifying the words LEFT, RIGHT and FULL, the word OUTER can be omitted. The word INNER can also be omitted in most DBMSs.

In general, the DBMS checks the condition (predicate) when executing the connection. For CROSS JOIN, the condition is not specified.

For cross-join (Cartesian product) CROSS JOIN, some SQL implementations use the comma operator (,):

select field [,… n] from Table1, Table2

SQL INNER JOIN

The INNER JOIN command returns rows that have matching values in both tables.

The following SQL selects all orders with customer information:

Example

 

SELECT Orders.OrderID, Customers.CustomerName
FROM orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; Try it yourself »

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the Orders table that do not have matches in the Customers section, then these orders will not be shown!

The following SQL statement selects all orders with customer and shipper information:

Example

 

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

SQL LEFT JOIN

The LEFT JOIN command returns all rows from the left table and the corresponding rows from the right table. The result will be NULL on the right side if there is no match.

In the following SQL, all customers and any orders they may have will be selected:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

RIGHT OUTER JOIN

The right outer join operatorRIGHT OUTER JOIN joins two tables. The order of the tables for the operator is important because the operator is not symmetric.

The header of the result table is a concatenation (concatenation) of the headers of the tables being joined.

The body of the result is logically formed as follows. Let the left and right tables be joined according to the predicate (condition) p.

  1. The result includes the INNER JOIN of the left and right tables according to the predicate p.
  2. Then, you add to the result those right table entries that were not included in the inner join in step 1. For such records, the fields corresponding to the left table are populated with NULL values.

select * from Person right outer join City on Person.CityId = City.Id

Result:

Person.Name Person.CityId City.Id City.Name
Alex11London
Michael11London
John22Paris
NULLNULL3Prague

Example

 

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID; Попробуйте сами »

 

Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).

Оператор OUTER JOIN

In SQL, the OUTER JOIN statement is used much less frequently, but is sometimes very useful. Now we will consider two options for using this operator. The operator performs an asymmetrical outer join of two tables—that is, the order of the tables is important.

Examples on Joining Tables in SQL

1.Write a request that would be used by the operator INNER JOIN to receive all Orders for the buyer with the surname Krasnov.

SELECT onum, amt, odate, cname FROM orders INNERJOIN customers on orders.cnum = customers.cnum and cname = ‘Краснов’

2.Write a request that would display the names and cities of sellers whose commission is more than 20%.

SELECTDISTINCT(sname), city, comm FROM salespeople INNERJOIN orders on orders.snum = salespeople.snum and comm > 20

3.Write a request that would display the total amount of orders for the cities in which sellers work.

SELECTSUM(amt), salespeople.city FROM orders INNERJOIN salespeople on orders.snum = salespeople.snum GROUPBY salespeople.city

4.Repeat the previous request, but display all the cities, even if no transaction was made in them.

SELECTSUM(amt), salespeople.city FROM orders RIGHTOUTERJOIN salespeople on orders.snum = salespeople.snum GROUPBY salespeople.city

5.Write a request that would display the maximum amount of orders for the cities in which buyers live, even if no transaction was made in these cities.

SELECT customers.city, MAX(amt) FROM customers LEFTOUTERJOIN orders on orders.cnum = customers.cnum GROUPBY customers.city

Configuring the Database

Before you can write any queries, you need to set up the database.

PostgreSQL will be used for the examples in this article, but the queries and concepts shown here are easily applicable in any other modern DBMS (MySQL, SQL Server, etc.).

To work with our PostgreSQL database, we will use the interactive cli program psql. If you have another client installed, you can work with it perfectly!

First, let's create our database. Since we already have PostgreSQL installed, to create a new database, we can run the createdb psql command <data-name> in the terminal. I named my database fcc:


$ createdb fcc
Now let's use the interactive console (running the psql command) and connect to the newly created database using the command c <name-database>:
$ psql psql (11.5) Type "help" for help. john=# c fcc You are now connected to database «fcc» as user «john». fcc= #

 

Note: In the examples, I cleaned up the output to make them easier to read. So don't worry that the output shown here doesn't exactly match what you see in your terminal.

I advise you to work through the queries that we will compile, write them with me and run them. Working with examples, you will understand and remember much more than if you just read.

CROSS JOIN (cross join)

The simplest union we can do is CROSS JOIN or "Cartesian product".

With this join, we take each row of one table and join it to each row of another table.

If we have two lists and one of them contains the numbers 1, 2, 3, and the other contains the letters A, B, C, then the Cartesian product of these lists will look like this:

1A, 1B, 1C 2A, 2B, 2C 3A, 3B, 3C

Each value in the first list is connected to each value in the second list.

Let's rewrite this example as a SQL query.

First, let's create two very similar tables and enter data into them:

CREATE TABLE letters( letter TEXT ); INSERT INTO letters(letter) VALUES (‘A’), (‘B’), (‘C’); CREATE TABLE numbers( number TEXT ); INSERT INTO numbers(number) VALUES (1), (2), (3);

Our letters and numbers tables have one column each with simple text fields.

Now let's combine these tables using CROSS JOIN:

SELECT * FROM letters CROSS JOIN numbers; letter | number ———+——— A | 1 A | 2 A | 3 B | 1 B | 2 B | 3 C | 1 C | 2 C | 3 (9 rows)

This is the simplest kind of joining, but even with this example we can see how JOIN works. Two different rows (one from the letters table, the other from numbers) are combined with each other to form a single row.

Although this example is often referred to as purely educational, and there is a practical application for it: covering the date range.

CROSS JOIN with date ranges

 

A good use case for CROSS JOIN is to take each row of the table and merge it every day from the date range.

Let's say you create an app that has to track your daily routine (brushing your teeth, breakfast, showering).

If you want to generate a record for each task for each day last week, you can use CROSS JOIN with a date range.

To create a date range, we can use the generate_series function:

SELECT generate_series( (CURRENT_DATE — INTERVAL ‘5 day’), CURRENT_DATE, INTERVAL ‘1 day’ )::DATE AS day;

The generate_series function takes three parameters.

The first parameter is the start value. In this example, we used the CURRENT_DATE , INTERVAL '5 day', that is, the current date minus five days (or "last five days").

The second parameter is the current date (CURRENT_DATE).

The third parameter is step. That is, how much we want to increment the value. Since these are daily tasks, we set the interval to one day (INTERVAL '1 day').

All together, it generates a series of dates, starting from a date five days ago and ending with today, day at a time.

Finally, we remove the time part, converting the output of values to a date using ::D ATE, and assign an alias to this column (using AS day) to make the output more beautiful.

Query result for the last five days plus today's:

day ———— 2020-08-19 2020-08-20 2020-08-21 2020-08-22 2020-08-23 2020-08-24 (6 rows)

Let's go back to our example with daily tasks. Let's create a simple table that will contain our tasks (and add a few):

CREATE TABLE tasks( name TEXT ); INSERT INTO tasks(name) VALUES (‘Brush teeth’), (‘Eat breakfast’), (‘Shower’), (‘Get dressed’);

In our tasks table, there is only one column, name, to which we have added several tasks.

Now let's cross-merge our tasks with the date generation request:

SELECT tasks.name, dates.day FROM tasks CROSS JOIN ( SELECT generate_series( (CURRENT_DATE — INTERVAL ‘5 day’), CURRENT_DATE, INTERVAL ‘1 day’ )::DATE AS day ) AS dates

(Since our query for generating dates isn't really a table, we just wrote it as a subquery.)

As a result, we get the name of the task and the day. It looks like this:

name | day —————+———— Brush teeth | 2020-08-19 Brush teeth | 2020-08-20 Brush teeth | 2020-08-21 Brush teeth | 2020-08-22 Brush teeth | 2020-08-23 Brush teeth | 2020-08-24 Eat breakfast | 2020-08-19 Eat breakfast | 2020-08-20 Eat breakfast | 2020-08-21 Eat breakfast | 2020-08-22 … (24 rows)

As expected, we got a row for each task for each day from our date range.

CROSS JOIN is the simplest association. Further examples will require more "vital" configuration of the tables.

FULL OUTER JOIN

The full outer join operator FULLL OUTER JOIN joins two tables. The order of the tables for the operator is unimportant because the operator is symmetric.

The header of the result table is a concatenation (concatenation) of the headers of the tables being joined.

The body of the result is logically formed as follows. Suppose the first and second tables are joined according to the predicate (condition) p. The words "first" and "second" here do not denote the order in the record (which is unimportant), but are used only to distinguish the tables.

  1. The result includes the INNER JOIN of the first and second tables according to the predicate p.
  2. The result adds those records in the first table that were not included in the inner join in step 1. For such records, the fields corresponding to the second table are populated with NULL values.
  3. The result is added to those records in the second table that were not included in the inner join in step 1. For such records, the fields corresponding to the first table are populated with NULL values.

select * from Person full outer join City on Person.CityId = City.Id

Result:

Person.Name Person.CityId City.Id City.Name
Alex11London
Michael11London
John22Paris
NULLNULL3Prague
Brad4NULLNULL

Create directors and movies tables

To illustrate the following types of associations, we'll use the movies and movie directors example.

Every film has a director, but that's not a requirement. There may be a situation where the film has already been announced, but the director has not yet been chosen.

Our directors table will store the names of all directors, and the movies table will contain the names of the films, as well as a reference to the director (if he is known).

Let's create these tables and enter the necessary data into them:

CREATE TABLE directors( id SERIAL PRIMARY KEY, name TEXT NOT NULL ); INSERT INTO directors(name) VALUES (‘John Smith’), (‘Jane Doe’), (‘Xavier Wills’) (‘Bev Scott’), (‘Bree Jensen’); CREATE TABLE movies( id SERIAL PRIMARY KEY, name TEXT NOT NULL, director_id INTEGER REFERENCES directors ); INSERT INTO movies(name, director_id) VALUES (‘Movie 1’, 1), (‘Movie 2’, 1), (‘Movie 3’, 2), (‘Movie 4’, NULL), (‘Movie 5’, NULL);

We have five directors and five films, with directors listed for three films. A director with ID 1 has two films, and a director with ID 2 has one film.

Multi-table queries

Using JOIN, you can combine not only two tables, as described above, but also much more. In MySQL 5.0 today, you can combine up to 61 tables. In addition to joining different tables, MySQL allows you to merge a table with itself. However, in any case, you need to monitor the names of columns and tables, if they are ambiguous, the query will not be executed.

So, if the table is simply merged onto itself, then a name conflict will arise and the query will not be executed.

Code — Merge the table on itself
mysql> SELECT * FROMnomenclatureJOINnomenclature; ERROR1066 (42000): Notuniquetable/alias: ‘nomenclature’

To bypass the name conflict, you can use aliases for table and column names. In the following example, the inner union will work more successfully:

Code — Merge the table on itself
mysql> SELECT * FROMnomenclatureJOINnomenclatureASt2; +—-+————+—-+————+ | id | name | id | name | +—-+————+—-+————+ | 1 | Book | 1 | Book | | 2 | | stool 1 | Book | | 3 | Pencil | 1 | Book | | 1 | Book | 2 | stool | | 2 | | stool 2 | stool | | 3 | Pencil | 2 | stool | | 1 | Book | 3 | Pencil | | 2 | | stool 3 | Pencil | | 3 | Pencil | 3 | Pencil | +—-+————+—-+————+9rowsinset (0.00sec)

MySQL does not impose restrictions on the use of different types of unions in one query, so you can form quite complex constructs:

Code - Example of a Complex Table Join
mysql> SELECT * FROMnomenclatureASt1JOINnomenclatureASt2LEFTJOINnomenclatureASt3ONt1.id = t3.idANDt2.id = t1.id; +—-+————+—-+————+——+————+ | id | name | id | name | id | name | +—-+————+—-+————+——+————+ | 1 | Книга | 1 | Книга | 1 | Книга | | 2 | Табуретка | 1 | Книга | NULL | NULL | | 3 | Карандаш | 1 | Книга | NULL | NULL | | 1 | Книга | 2 | Табуретка | NULL | NULL | | 2 | Табуретка | 2 | Табуретка | 2 | Табуретка | | 3 | Карандаш | 2 | Табуретка | NULL | NULL | | 1 | Книга | 3 | Карандаш | NULL | NULL | | 2 | Табуретка | 3 | Карандаш | NULL | NULL | | 3 | Карандаш | 3 | Карандаш | 3 | Карандаш | +—-+————+—-+————+——+————+9rowsinset (0.00sec)

in addition to selections you can also use joins in update and delete requests

So, the following three queries do the same job:

Code - Multi-Tab Updates
mysql> UPDATEnomenclatureASt1, nomenclatureASt2SETt1.id = t2.idWHEREt1.id = t2.id; QueryOK, 0rowsaffected (0.01sec) Rowsmatched: 3Changed: 0Warnings: 0mysql> UPDATEnomenclatureASt1JOINnomenclatureASt2SETt1.id = t2.idWHEREt1.id = t2.id; QueryOK, 0rowsaffected (0.00sec) Rowsmatched: 3Changed: 0Warnings: 0mysql> UPDATEnomenclatureASt1JOINnomenclatureASt2USING(id) SETt1.id = t2.id; QueryOK, 0rowsaffected (0.00sec) Rowsmatched: 3Changed: 0Warnings: 0

Multiple deletions work in the same way

Code - Multi-Table Deletions
mysql> DELETEt1FROMnomenclatureASt1JOINnomenclatureASt2USING(id) WHEREt2.id > 10; QueryOK, 0rowsaffected (0.02sec)

Keep in mind that when you use multi-table queries to delete or update data, you cannot include the ORDER BY and LIMIT constructs in the query. However, this limitation is very effectively circumvented with the help of temporary tables, simply, you need to take this into account when modifying single-table queries.

Examples of using multi-table queries

I will give a few examples from my practice that are really used.

Code — Example #1 of a multi-table query
SELECTSQL_CALC_FOUND_ROWSdgs.dogovor_id, dgs.dogovor_name, dgs.abonent_name, dgs.abonent_type, dgs.address_fiz, dgs.date_conclusion, dgs.date_annulment, dgs.threshold, ubc.usumFROMbilling_dogovorsdgsLEFTJOINbilling_users_balanceubcONubc.udate = CURDATE() ANDdgs.dogovor_id = ubc.dogovor_idWHEREdgs.dogovor_nameLIKE»%123%»ORDERBYdgs.dogovor_nameASCLIMIT0, 58
 

In this case, there is a sample of the first 58 clients from the table of contracts with the binding of the balance for the current day, whose contract name contains "123" and sorting by the name (number) of the contract. Since the list of contracts may not coincide with the list of balances, a left-hand association is used. In addition, SQL_CALC_FOUND_ROWS is used to calculate the total number of rows found in order to organize page navigation.

Code — Example #2 of a real multi-table query
SELECTSQL_CALC_FOUND_ROWSpft.udate, dgs.dogovor_name, pft.usum, ptt.type_nameFROMbilling_profitpftLEFTJOINbilling_dogovorsdgsUSING( dogovor_id ) LEFTJOINbilling_profit_typespttONpft.profit_type = ptt.type_idWHEREpft.udate > CURDATE() — INTERVAL7DAYORDERBYpft.udateDESC, dgs.dogovor_nameASCLIMIT0, 30;
 

This request displays a list of payments indicating the type of payment and the contract number, sorted by date and contract number. A page-by-page list is also provided.

Code — Example #3 of a real mono-table query
SELECTSQL_CALC_FOUND_ROWSips.ip, ips.segment_id, ips.gray_ip, ips.ip_mac, ips.ip_status, ips.ip_type, ips.blocked_reason, ips.blocked_time, ips.comment, rts.router_name, dgs.dogovor_name, ipt.type_name, ubc.usumFROMbilling_ipsipsLEFTJOINbilling_routersrtsONips.segment_id = rts.router_idLEFTJOINtONips.ip = t.ipLEFTJOINbilling_ip_typesiptONips.ip_type = ipt.type_id, billing_dogovorsdgsLEFTJOINbilling_users_balanceubcONubc.udate = CURDATE() ANDdgs.dogovor_id = ubc.dogovor_ idWHEREINET_NTOA(ips.ip) LIKE»%123%»ANDdgs.dogovor_nameLIKE»%123%»ANDdgs.dogovor_id = t.dogovor_idORDERBYips.ipASCLIMIT0, 80
 

This example uses one internal union to bind IP addresses to contracts, and three external left-hand merges for more information. Despite the impressive size, the request is executed quite quickly, since the aggregations are based on primary keys. Since the result should be a list of contracts and ip addresses associated with them, then internal aggregation is used.

In practice, it is necessary to use more exotic constructs infrequently, because with the increase in the number of merged tables, the performance of queries drops sharply. Complex problems that require serious calculations, such as, for example, counting the balances of all customers, are solved with the help of temporary tables and fairly simple queries.