SQL Server Technology Insider 3 join query

The join table operator operates on two input tables. There are three basic types of joins: Cross joins, Inner joins, and outer joins. The difference between the three types of joins is that they take different logical query processing steps, each of which has a set of steps. The cross join has only one step----cartesian product, the inner join has two steps----Cartesian product, filtering, and the outer join has three steps----Cartesian product, filtering, adding outer rows.

3.1 Cross Join

SQL Server supports two standard syntaxes for cross joins: ANSI SQL-92 and ANSI SQL-89, which are recommended for use with SQL-92 syntax.

3.1.1 ANSI SQL-92 Syntax

SELECT C.custid, E.empidfrom sales.customers as C cross  JOIN HR. Employees as E;

Using the ANSI SQL-92 syntax, to use the cross join keyword between the two tables participating in a join

3.1.2 ANSI SQL-89 Syntax

SELECT C.custid, E.empidfrom sales.customers as C, HR. Employees as E;

In this syntax, simply add a comma to the table name.

3.2 Inner Joins

3.2.1 ANSI SQL-92

SELECT e.empid, E.firstname, E.lastname, O.orderidfrom HR. Employees as E  JOIN sales.orders as O    on = o.empid;

With the ANSI SQL-92 syntax, the INNER JOIN keyword must be specified between two table names, and the inner keyword is optional because the inner join is the default join method. The ON clause is used to filter the rows, returning only the rows that evaluate to true, without returning a row that evaluates to False or unknown.

3.2.2 ANSI SQL-89

SELECT e.empid, E.firstname, E.lastname, O.orderidfrom HR. Employees as E, sales.orders as OWHERE= o.empid;

Inner joins can also be expressed in ANSI SQL-89 syntax, where you can place a comma between table names like a cross join, and then define join conditions with a WHERE clause.

3.2.3 Why is it recommended to use the ANSI SQL-92 syntax

1. If you want to write an inner join query, but accidentally forget to specify the join condition, if you are using the ANSI SQL-89 syntax, then the runtime will not have the exception information, but the execution is a cross-query. However, if the ANSI SQL-92 syntax is used at this point, the parser will error.

2. If you want to write a cross-join query, if you are using ANSI SQL-89 syntax, and then other developers are looking at or maintaining your code, then how do they know if you want to write a cross join statement or write an inner JOIN statement, but forget to specify the join condition? But if you use the ANSI SQL-92 syntax and display the CROSS join keyword, you can explicitly express what you want to do with a crossover join.

3.3 Outer Joins

Outer joins apply two logical processing steps (Cartesian product and on filtering) applied to the inner join, plus a third step specific to the outer join: Adding an outer row . In an outer join, to mark a table as a "reserved table", you can use the keyword left OUTER join, the right OUTER join, and the full OUTER join between table names, where the OUTER keyword is optional. The Left keyword indicates that the row for the table on the right is reserved, and that the row for the table on the side is reserved, and full indicates that the rows of the table on both sides are reserved.

The third step in the outer join is to identify those rows in the reserved table that match the on condition in the other table, and then add those rows to the result table generated by the first two steps of the join, and for those columns from the joined non-reserved table, the columns in the appended outer row are null as placeholders.

The following query joins the customer and Orders tables based on the client ID of the Customer table and the customer ID of the order table, and returns the customer and their order information. The join type used by the query statement is a LEFT outer join, so the query results also return customers who have not made any orders.

SELECT C.custid, C.companyname, O.orderidfrom sales.customers as C  Left OUTER JOIN sales.orders as O    = O.custid;

If you now only need to return to a customer who has not placed any orders, or in more technical terms, just return to the outer row. You can use the following query to process

SELECT C.custid, C.companyname, O.orderidfrom sales.customers as C  Left OUTER JOIN Sales.orders as O    =WHERE O.orderid is NULL;

It is also important to choose which column to use as a filter when returning an external row. You should select only the outer row to take a value of NULL, and a column with a value that is not NULL in the other rows. There are three scenarios in which you can consider safe use: primary key columns, join columns, and columns defined as NOT NULL . The column value of the primary key cannot be null, so if NULL appears on such a column, it means that the behavior is an outer row. If a row's join is listed as a null value, the row is filtered out in the second step of the join, so if the value of the join column for a row is null, the behavior is the outer row. Similarly, a null value appears on a column that is defined as NOT NULL, and the row is obviously an outer row.

SQL Server Technology Insider 3 join query