Intra-SQL Server connections, outer joins, cross connections

Preface

In database queries, we often use tables to connect queries, and I use these tables to connect to queries myself at work. And just now I haven't figured out what kinds of database table connections are there,

These kinds of table connection query way and what difference, it is ashamed! Take this article to bear in mind.

Connection Mode

database table connection query three kinds: inner connection, outer connection, cross connection

So Let's talk about these three kinds of connections separately.

Internal connection (inner join)

The inner connection is also divided into: equivalent connection, unequal connection, natural connection

1. Equivalent connection

When we join a multi-table query, the join condition uses the equals (=) operator, and its query results list all the columns in the joined table, including the repeating columns .

2, unequal connection

We use operators other than equals sign (=) in join conditions when connecting multiple table queries (>, <, <>, >=, <=,!>, and!<)

3. Natural connection

When we connect a multi-table query, the join condition is the same as the equivalent connection, but the duplicate columns in the Join table are deleted .

outer JOIN (outer join)

The outer joins are divided into: Left join (left join) or off-the-outside connection (the ieft outer join), right-join, or right-side-joins, or full-connection (full-join) or all-out (fully outer join).

1, left JOIN connect

When we concatenate a multi-table query, all rows in the left table are returned, and if the rows in the left table have no matching rows in the right table, the columns in the right table in the result return null values.

2. Right connection

When we join a multi-table query, we return all the rows in the right table as opposed to left join, and if the rows in the right table have no matching rows in the left table, the columns in the left table in the result return null values.

3. Full connection

When we join a multi-table query, we return all rows from the left and right tables. When a row does not have a matching row in another table, the columns in the other table return null values.

Cross Join

Cross connection: Also known as Cartesian product

Without a WHERE clause, it returns the Cartesian product of the two connected tables, the number of rows that return the result is equal to the product of two table rows, and if there is a where, the number of matching rows is returned or displayed.

Note: crossjoin后加条件只能用where,不能用on

Intra-SQL Server connections, outer joins, cross connections