SQL table join diagram, SQL table diagram

SQL table join diagram, SQL table diagram

You can see through the figure below

Multi-table queries are classified into internal and external connections.

Outer join is divided into left join or left outer join, right join or right outer join, and full outer join)

The result of left join (left join or left outer join) is all rows in the left table in the left join clause, not just the rows matched by the link column, if a row in the left table does not match in the right table, all selected columns in the right table in the associated result row are NULL)

SQL syntax select * from table1 left join table2 on table1. condition column name = table2. condition column name;

Note: All columns in Table 1 and matching columns are displayed.

Right join (right join or right outer join) is not mentioned here. This left join is very similar but opposite. Just talk about the syntax.

Select * from table1 right join table2 on table1. condition column = table2. condition column

Full outer join or full outer join)

Displays all rows in the left and right tables. If no matching row exists in one table, the selection list column of the other table contains NULL values. If yes, all data is displayed.

SQL Syntax:

Select * from table1 full join table2 on table1. condition column name = table2. condition column name

Internal Connection:
Concept: inner join is to use a comparison operator to compare the join of values in the connected column.

Internal join (join or inner join)

SQL Syntax:

Select * fron table1 join table2 on table1. condition column name = table2. condition column name

Returns two matching columns.

It is equivalent:

Select A *, B * from table1 A, table2 B where A. condition column name = B. condition column name
Select * form table1 cross join table2 where table1. condition column name = table2. condition column name (Note: Cross join cannot be followed by on but can only use where)

Cross join (complete)

Concept: without the cross join of the where clause, the number of rows in the first table of the Cartesian product involved in the join is multiplied by the number of rows in the second table, which is equal to the Cartesian Product and the size of the result set.

Cross join: Cross join (without the condition where, if the returned or displayed Number of matched rows)

SQL Syntax:

Select * from table1 cross join table2

If a condition (where) exists)

Select * from table1 cross join table2 where table1. condition column name = table2. condition column name

Equivalent

Select * from table1, table2 (without where)