Have you really learned MySQL connection query?

1. Inner Join Query Overview

Inner join is a very common join operation in applications and is generally the default join type. Inner join is based on join predicate, which combines the columns of two tables (such as A and B) to produce a new result table. The inner join query compares each row of table A with each row of table B and finds the combination that satisfies the join predicate. When the join predicate is satisfied, the matching rows in A and B are combined column-wise (side-by-side) into a single row in the result set.

The inner join query operation lists the data rows that match the join conditions, and it uses comparison operators to compare the column values ​​of the joined columns. There are three types of inner joins: cross join, equal join and natural join.

2. Cross Join (Cartesian Product)

Cross Join, also known as "Cartesian Join" or "Product", is the basis of all types of inner joins.

If the table is regarded as a collection of row records, then the cross join returns the Cartesian product of the two collections, and the number of data rows returned in the result set is equal to the number of data rows in the first table that meet the query conditions multiplied by the number of data rows in the second table that meet the query conditions. This is actually equivalent to the inner join condition being "always true" or the join condition not existing. If A and B are two sets, their cross join is written as: A x B.

Define a cross join (Cartesian product) of the routes table and the vehicles table.

Example:

Implement the Cartesian product of the route table and the vehicle table

SELECT COUNT(*)
FROM line 
CROSS JOIN vehicle

3. Inner Join Syntax and Examples

grammar:

SELECT fieldlist
FROM table1 [INNER] JOIN table2 
  ON table1.column1=table2.column2
[WHERE condition]

Example 1:

Use inner join to obtain vehicle information and driver information whose vehicle model contains "DD". The vehicle information is required to be all columns, and the driver information only needs to include the name and ID card.

SELECT v.*,d.name,d.licenseNo
FROM vehicle v JOIN driver d
  ON v.driverID=d.driverID
WHERE model LIKE '%DD%'

Example 2:

Use inner join to obtain the information of drivers over 35 years old and the vehicle information driven by the driver. The vehicle information is required to be all columns, and the driver information only contains the name, date of birth and phone number.

SELECT v.*,d.name,d.licenseNo,d.phone
FROM vehicle v INNER JOIN driver d
  ON v.driverID=d.driverID
WHERE CEIL(DATEDIFF(NOW(),birthday)/365)>35

4. Natural connections

A natural join is a special inner join that requires that the connection basis columns of the two tables to be connected must be the same fields (the fields are the same and the field attributes are the same). In a natural join, all columns with the same name in the two tables will be compared, and the duplicate columns will be removed from the result set. The columns with the same name in the two tables will only appear once in the result set. Ordinary inner joins do not remove duplicate columns. (Some databases do not support natural connections, such as saL Server.)

The syntax for a natural join is as follows:

SELECT fieldlist
FROM table1 NATURAL JOIN table2
[WHERE condition]

Example:

Use natural join to obtain vehicle information and driver information whose vehicle model contains "DD". The vehicle information is required to be all columns, and the driver information only needs to include the name and ID card.

SELECT v.*,d.name,d.licenseNo
FROM vehicle v NATURAL JOIN driver d
WHERE model LIKE '%DD%'

5. Multi-table join query

If the queried information comes from multiple tables, a multi-table join query can be established by connecting two tables together.

The following is the syntax for a three-table join query:

SELECT fieldlist 
FROM table1 JOIN table2 
  ON table1.column1=table2.column2_1
JOIN table3
  ON table2.column2_2=table3.column3
[WHERE condition]

illustrate:

(1) table2 is connected to table1 and table3.

(2) The method for joining more than three tables is the same as that for joining three tables, which is implemented by connecting two tables together.

Example 1:

Get the license plate number, model and driver name of all non-air-conditioned vehicles, the route of the route, the starting station and the terminal station information

SELECT d.name,v.plateNo,v.model,l.lineNo,l.from_station,l.end_station
FROM vehicle v JOIN driver d
  ON v.driverID=d.driverID
JOIN line l
  ON v.lineID=l.lineID
WHERE type = 'Non-air-conditioned car'

Example 2:

Get all the driver information of Bus Company 2. The driver's name, ID card, gender and phone number are required to be output.

SELECT d.name,d.licenseNo,d.gender,d.phone
FROM vehicle v JOIN driver d 
  ON v.driverID 
JOIN line l
  ON v.lineID=l.lineID
WHERE company = 'Bus Company 2'

6. Simple multi-table join query

If you directly list all the tables to be joined in the FROM clause and then specify the join conditions in the WHERE clause, this is a simple multi-table query, which has the same function as an inner join.

The query syntax for joining two tables is as follows:

SELECT fieldlist
FROM table1,table2
WHERE table1.column1=table2.column2
[AND other conditions]

The query syntax format using three table join is as follows:

SELECT fieldlist 
FROM table1,table2,table3
WHERE table1.column1=table2.column2_1 
  AND table2.column2_2=table3.column3
[AND other conditions]

Example:

Get the license plate number, model and driver name of all non-air-conditioned vehicles, the route of the route, the starting station and the terminal station information

SELECT d.name,v.plateNo,v.model,l.lineNo,l.from_station,l.end_station
FROM vehicle v,driver d,line l
WHERE v.driverID=d.driverID
  AND v.lineID=l.lineID
AND type='Air-conditioned car'

Summarize

This is the end of this article about MySQL connection query. For more relevant MySQL connection query content, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!