Intra and outer joins in SQL and cross-join usage

(i) Internal connections
SQL INNER JOIN keyword
When there is at least one match in the table, the INNER JOIN keyword returns a row.
The INNER JOIN query operation lists the rows of data that match the join criteria, which compares the columns of the connected column with the comparison operator.
The column value. The inner connection is divided into three kinds:
1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected columns, whose query knot
The results list all the columns in the attached table, including the repeating columns.

Grammar

The code is as follows	Copy Code
SELECT column_name (s) From table_name1 INNER JOIN table_name2 On Table_name1.column_name=table_name2.column_name

Note: INNER join is the same as join

For example, the following uses an equivalent connection to list authors and publishers in the same city in the authors and Publishers tables:

The code is as follows	Copy Code
SELECT * From authors as a INNER JOIN publishers as P On a.city=p.city

2. Unequal connection: The connection condition uses comparison operators other than the equals operator to compare the connected
The column value of the column. These operators include >, >=, <=, <,!>,!<, and <>.
3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected columns, but it uses the selected
An optional list indicates which columns are included in the query result collection and deletes duplicate columns from the attached table.
For example, delete the repeating columns (city and state) in the authors and Publishers tables in the select list:

The code is as follows	Copy Code
SELECT A.*,p.pub_id,p.pub_name,p.country From authors as a INNER JOIN publishers as P On a.city=p.city

(ii) outer joins　　

The left JOIN keyword returns all rows from the left-hand table (TABLE_NAME1), even if there are no matching rows in the right table (table_name2).

Outer joins, which are returned to the query result collection to include not only rows that meet the join criteria, but also the left table (left
Outer or left connection), all rows of data in the right table (right outer or right) or two side tables (full outer joins).

A LEFT JOIN, which returns records that include all the records in the left table and the join fields in the right table are equal;
Right join, which returns records that include all the records in the right table and the join fields in the left table are equal;

Left JOIN keyword syntax

The code is as follows	Copy Code
SELECT column_name (s) From table_name1 Left JOIN table_name2 On Table_name1.column_name=table_name2.column_name

Note: In some databases, the left join is called the left OUTER join.

For example 1:

The code is as follows	Copy Code
SELECT a.*,b.* from Luntan left JOIN usertable as B On A.username=b.username

For example 2:

The code is as follows	Copy Code
SELECT a.*,b.* From city as a full OUTER JOIN user as B On A.username=b.username

(iii) Cross-linking 　　
The cross join does not take a WHERE clause, which returns the Cartesian product of all data rows of the connected two tables, and returns to the
The number of rows in the result set equals the number of data rows in the first table that match the query criteria, multiplied by the second table.
The number of data rows to which the criteria are consulted. For example, there are 6 categories of books in the titles table, and there are 8 publishers in the publishers table.
The number of records retrieved by a column cross join is equal to the 6*8=48 line.
For example:

The code is as follows	Copy Code
SELECT Type,pub_name From titles CROSS JOIN Publishers ORDER BY Type

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

If we want to list everyone's order, we can use the following SELECT statement:

The code is as follows	Copy Code
SELECT Persons.lastname, Persons.firstname, Orders.orderno From Persons INNER JOIN Orders On persons.id_p = orders.id_p ORDER BY Persons.lastname