SQL join usage 1

Before describing the use of join, I first reference the most common paradigm in database design.

3nf: If all non-primary attributes in the relational model R (u, f) do not transmit trust to any candidate keywords, the relational model R belongs to the 3nf.

For example, each attribute of S1 (SNO, sname, DNO, dname, location) represents a student ID, name, Department, Department name, and Department address.

The SNO keyword determines the attributes. Because it is a single keyword, there is no part of the dependency problem, it must be 2nf. However, there must be a lot of redundancy in this relationship. The attributes DNO, dname, and location of the student will be stored, inserted, deleted, and modified repeatedly.

Cause: A dependency is transmitted in the link. Because sname and DNO depend on SnO, and dname and location depend on DNO, a student's name and code can be known through a student ID, however, the student ID cannot know the Department address. The student ID and the Department address are associated by the department of the student corresponding to the student ID, therefore, the sno keyword depends on the location function by passing the dependency DNO-> location. That is to say, SnO does not directly determine the non-primary attribute location.

Solution: the transfer dependency cannot be left in each link mode.

Solution: There are two relationships: S (SNO, sname, DNO), D (DNO, dname, location)

Note: The outer keyword DNO cannot be found in link S. Otherwise, the relationship is lost.

In the database design process, the database is often designed according to the third paradigm. Of course, in some cases, related redundant fields are added to optimize the database performance so that the table structure does not conform to 3nf, in most cases, no table can fully utilize the result set required by the customer. In this way, you need to join multiple tables with logical dependencies and select the data you need.

Before using join, you must understand that data is retrieved from the relevant table based on the logical relationship between tables through join. With the help file provided by SQL Server, you can clearly know that You can specify a join in the from or where clause.

1.1. Specify the join in the WHERE clause

In the following example, the where clause is used

Select a. symbol, A. sname, B. tdate, B. Close

From securitycode A, dayquote B

Where a. symbol = B. Symbol

And B. tdate> = A. listdate

And a. symbol like '123'

In the preceding example, table A and table B are joined by the. symbol = B. symbol condition, and the filtering condition is B. tdate not less than a. listdate.

Specifying a join in the WHERE clause may be more convenient for simple joins. However, we do not recommend using this syntax to join tables.

1.2. Specify a join in the from clause

Taking the example above for details, the table securitycode mainly stores the basic information of the securities code. the symbol in the table represents the stock code, the sname represents the stock name, And the listdate represents the stock date; table dayquote stores the daily stock prices. Symbol indicates the stock code, tdate indicates the trading date, and close indicates the closing price.

The logic of my current value is to retrieve the closing price of all trading days of the stock with the stock code hitting 600 since the date of listing. Connect securitycode and dayquote through symbol.

Select a. symbol, A. sname, B. tdate, B. Close

From securitycode

Join dayquote B

On a. symbol = B. Symbol

Where B. tdate> = A. listdate

And a. symbol like '123'

Order by A. symbol, B. tdate

When using the from clause to join a table, you can clearly see the join conditions between tables. It is more readable and can be modified later than the WHERE clause.

The following describes how to reference the Help file by means of join. Join can be divided into the following types:

1.3. Inner join

Inner join (a typical join operation that uses comparison operators such as = or <> ). Including equal join and natural join.

The inner join uses the comparison operator to match rows in two tables based on the values of the columns in each table.

In the SQL-92 standard, the inner join can be specified in the from or where clause. This is the only join type supported by a SQL-92 in the where clause. The inner join specified in the WHERE clause is called the old-style inner join.

Common internal join statements:

Select a. column1, [A. column2], B. column1, [B. column2]

From Table1

[Inner] Join Table2 B

On a. column0 = B. column0

Inner is often omitted when inner join is used in the query analyzer.

1.4. Outer Join

Outer Join can be left Outer Join, right outer join, or complete external join.

When an external join is specified in the from clause, it can be specified by one of the following sets of keywords:

Left join or left Outer Join

The result set of the left Outer Join includes all rows in the left table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all selection list columns in the right table in the row of the associated result set are null.

Right join or right Outer Join

The right outer join is the reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.

Full join or full outer join

The Complete External Join Operation returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.

Rows are returned only when at least one row in the same two tables meets the join conditions. The inner join removes rows that do not match any row in the other table. The outer join will return all rows of at least one table or view mentioned in the from clause, as long as these rows meet any where or having search conditions. Searches all rows in the left table referenced by the left Outer Join and all rows in the right table referenced by the right outer join. All rows of the two tables in the complete external join will be returned.

Left join is a common external join. Familiar with left join can solve most of the problems.

General Syntax of Outer Join:

Select a. column1, [A. column2], B. column1, [B. column2]

From Table1

Left | right | full [outer] Join Table2 B

On a. column0 = B. column0

Outer is often omitted when you use outer join in the query analyzer. Left and right are only Direction problems. In certain cases, full outer join is equivalent to the union of left Outer Join and right outer join to eliminate duplicate rows.

1.5. Cross join

Returns all rows in the left table. Each row in the left table is combined with all rows in the right table. Cross join is also called Cartesian product.

A cross join without a where clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. That is to say, without the WHERE clause, if Table A has three rows of records, table B has six rows of records ::

Select a. *, B. * from Table A cross join Table B

The above statement will return 18 rows of records.

This article reposted from a good blog, the original source: http://www.phpweblog.net/zwws/archive/2007/02/23/946.html