SQL internal connection and external connection and cross connection usage

This article introduces the usage and examples of internal connection and external connection and cross connection in mysql. If you need to know more, refer to the introduction.

This article introduces the usage and examples of internal connection and external connection and cross connection in mysql. If you need to know more, refer to the introduction.

(1) inner connection
SQL INNER JOIN keyword
If at least one match exists in the table, the inner join keyword returns the row.
The inner join query operation lists the data rows that match the connection condition. It uses the comparison operator to compare
Column value. Intranet connections are divided into three types:
1. equijoin: Use the equal sign (=) operator in the connection condition to compare the column values of the connected column.
If this parameter is set, all columns in the connected table, including duplicate columns, are listed.

Syntax

The Code is as follows:
SELECT column_name (s) FROM table_name1 Inner join table_name2 ON table_name1.column_name = table_name2.column_name

Note: inner join and JOIN are the same.

For example, the following uses equijoin to list authors and publishers in the same city in the authors and publishers tables:

The Code is as follows:
SELECT * FROM authors AS a inner join publishers AS p ON a. city = p. city

2. Unequal join: Use a comparison operator other than the equal operator to compare the connected
Column value. These operators include >,>=, <=, <,!> ,! <和<> .
3. Natural join: Use the equal to (=) operator in the connection condition to compare the column values of the connected column, but it uses
Select the list to indicate the columns included in the query result set and delete the duplicate columns in the connection table.
For example, in the select list, delete the duplicate columns (city and state) in the authors and publishers tables ):

The Code is as follows:
SELECT a. *, p. pub_id, p. pub_name, p. country FROM authors AS a inner join publishers AS p ON a. city = p. city

(2) External Connection　　

The left join keyword returns all rows from the LEFT table (table_name1), even if no matching row exists in the right table (table_name2.

The returned results include not only the rows that meet the connection conditions, but also the left table (left
All data rows in the outer join or left join table), right table (right Outer Join or right join), or two edge join tables (all Outer Join.

Left join returns records that include all records in the left table and join fields in the right table;
Right join returns records that include all records in the right table and join fields in the left table;

Left join keyword syntax

The Code is as follows:
SELECT column_name (s) FROM table_name1 Left join table_name2 ON table_name1.column_name = table_name2.column_name

Note: In some cases, left join is called left outer join.

Example 1:

The Code is as follows:
SELECT a. *, B. * FROM luntan left join usertable as B ON a. username = B. username

Example 2:

The Code is as follows:
SELECT a. *, B .* FROM city as a full outer join user as B ON a. username = B. username

(3) cross join 　　
The cross join clause does not contain the WHERE clause. It returns the Cartesian product of all data rows in the two joined tables and returns
The number of rows in the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.
The number of data rows under the query condition. For example, there are 6 types of books in the titles table, and eight publishers in the publishers table
The number of records retrieved by column crossover is 6*8 = 48 rows.
For example:

The Code is as follows:
SELECT type, pub_name FROM titles cross join publishers Order by type

SQL JOIN-use Join
In addition to the above method, we can also use the keyword JOIN to obtain data from two tables.

If we want to list the orders of all users, we can use the following SELECT statement:

The Code is as follows:
SELECT Persons. LastName, Persons. FirstName, Orders. OrderNo FROM Persons Inner join Orders ON Persons. Id_P = Orders. Id_P Order by Persons. LastName