MySQL connection queries and subqueries

One, connection query

1. Cross-Connect

is to connect all the records in another table from one record in a table, and save all the records, including all the fields of two tables!

From the results, it is the Cartesian product of the two tables!

The Cartesian product is the result of all possible connections in two tables! If the first table has N1 records, the second table has N2 records, then the Cartesian product results have N1*N2 records!

Cross-Connection syntax:

SELECT * | Field List from Table 1 Cross Join Table 2

2. Internal connection

The data exists in the left table and the corresponding matching results in the right table are saved. matches are displayed . If there is no match, we think the data is meaningless and will not be saved.

The so-called mate is the existence of a condition that allows two or more tables to recognize each other. This is usually the same field that exists in both tables. The table name is generally very long, and you can take an alias .

Select *| Field List from Left Table [inner] join Right Table on Left Table . Field = Right Table . Field ;

3. External connection

The outer joins can also be divided into left outer joins (the Ieft [outer] join) and the right outer joins (the [outer] join). The former's left table is the main table, and the latter is the main table. The syntax is connected in the same.

If the match succeeds, all records of two tables are retained, and if the match fails (that is, one record of the left table cannot match all the records in the right table), only the records of the left table are kept, and the records of the right table are all null instead.

4. Natural connection

As long as two of the table has the same field name , the system is considered a connection condition, will be active to match the two same field name values are the same, if the same field name of two tables the same value, the match is considered successful; if you have more than one field name in two tables , then all the same field names have the same values to match the success!

1) Natural Inner Connection

Left table natural INNER join right table

A natural inner join will proactively delete a duplicate column and put the repeating column at the front

2) Natural External connection

Left Table Natural Left|right Join Right Table

Second, sub-query

The so-called sub-query, from the formal view, is a SELECT statement and another one or more SELECT statements appear. The most basic requirement: all subqueries need to be enclosed in parentheses!

1, according to the form of the subquery return value

1) Single value: that is, to return a single row of sub-query, also known as the scalar subquery//is often the result of the scalar quantum query as a value to use, such as to judge, participate in the operation and so on

2) A column: a subquery that returns a single row, also known as an array query//The result of an array query is often a collection of data of the same property, usually with the in and not in set operators.

3) One line : A subquery that returns a row is called a subquery

A row element is constructed during the query to be compared with the results of a subquery,select *| Field List from Table name where (Field 1 , the field 2 ... )=(row subquery result)

4) Multiple rows and columns: table sub-query

Table subqueries are generally from-type, that is, after the from, generally as a data source to use,select *| Field List from Sub-query results as aliases where clause ...

2, according to the location of the sub-query appears

From type: That is, the subquery appears after the From

Where type: That is, the subquery appears after the where

Exist type: Determine whether the results are queried

Exists is primarily used to make judgments, and the return result is a Boolean value! Select exists (clause);

Basis of judgment:

If a subquery can return data, the result of exists is true, otherwise false

The main function of the exists is to determine if the following SELECT statement has queried the data

Application Scenarios:

Registered users, you must ensure that the user name is not registered, at this time you can first query the user's user name exists!

MySQL connection queries and subqueries