Common Ways to merge SQL multi-table queries into a single statement

When combining data across multiple tables, it is sometimes difficult to figure out which SQL syntax to use. Here I will describe how to merge queries from multiple tables into a single declaration.

In this articleArticleThe sample query meets the sql92 ISO standard. Not all database manufacturers follow this standard, and the improvement measures taken by many manufacturers may have unexpected consequences. If you are not sure whether your database supports these standards, you can refer to the relevant information of the manufacturer.

Select

A simple SELECT statement is the most basic way to query multiple tables. You can call multiple tables in the from clause to combine results from multiple tables. Here is an example of how it works:

The following is a reference clip:

Select table1.column1, table2.column2 from Table1,

Table2 where table1.column1 = table2.column1;

In this example, I use the dot (table1.column1) to specify the table from which the column comes from. If the column involved only appears in a reference table, you do not need to add the full name, but adding the full name will help readability.

In the from clause, tables are separated by commas. You can add any number of tables as needed, although some databases have a limit on the content they can effectively process before introducing formal join declarations, this will be discussed below.

This syntax is a simple inner join. Some databases regard it as equivalent to an external join. The where clause tells the database which region to associate and returns results, just like combining the listed tables into a separate table under the given conditions. It is worth noting that your comparison condition does not need to be the same as the column you returned as a result group. In the preceding example, table1.column1 and table2.column1 are used to combine tables, but table2.column2 is returned.

You can use the and keyword in the WHERE clause to extend this function to more than two tables. You can also use this table combination to limit your results without actually returning columns from each table. In the following example, table3 matches Table1, but I didn't return anything from table3 for display. I just make sure that the related columns from Table1 exist in table3. In this example, table 3 needs to be referenced in the from clause.

The following is a reference clip:

Select table1.column1, table2.column2 from Table1,

Table2, table3 where table1.column1 =

Table2.column1 and table1.column1 = table3.column1;

However, it should be noted that the way to query multiple tables is a join. Your database may process things differently, depending on the Optimization engine it uses. Furthermore, ignoring the definition of the features related to the WHERE clause will give you undesirable results, for example, return the rogue field of the column related to each possible result from the remaining query, just like in cross join.

If you are used to the way your database processes this type of declaration, and you only combine two or a few tables, a simple SELECT statement can be used.

Join

Join works in the same way as the SELECT statement. It returns a result group with columns from different tables. The advantage of using external join on the implicit join is to have better control over your result group, and it may also improve performance when many tables are involved.

There are several join types: Left, right, full outer, inner, and cross. The type you use is determined by the result you want to see. For example, if left Outer Join is used, all related rows are returned from the first table listed, at the same time, if there is no information related to the first table, it will potentially Add rows from the second table listed.

Inner join is different from the implicit join. Inner join returns only rows with data in both tables.

Use the following join statement for the first SELECT query:

The following is a reference clip:

Select table1.column1, table2.column2 from Table1 inner join Table2

On table1.column1 = table2.column1;

Subquery

A subquery or a sub-selection statement is a way to use a result group as a resource in a query. It is often used to limit or define the results, rather than running multiple queries or manipulating data in the application software. With subqueries, you can refer to the table to determine the data content, or in some cases, return a column, which is the result of a subselection.

The following example uses two tables. A table contains the data that I want to return, while another table provides a comparison point to determine what data is actually of interest to me.

The following is a reference clip:

Select column1 from Table1 where exists

(Select column1 from Table2

Where table1.column1 = table2.column1 );

performance is an important aspect of subqueries. Convenience comes at a cost. It depends on the size, quantity, and complexity of the tables and declarations you use, and you may allow your applications to perform processing. Each query is processed independently before it is used as a resource by the primary query. If possible, the creative use of join statements can provide the same information with less latency.