Before I describe Join usage, I will first reference the most common paradigm in the database design.
Third Paradigm (3NF): If all non-primary properties in the relational mode R (U, F) do not pass trust on any candidate keyword, the relational R is a third paradigm.
Example: If S1 (Sno, Sname, DNO, DNAME, LOCATION) each attribute represents a student number, name, a system, a system name, a system address.
Keyword SNO determines each attribute. Since it is a single keyword, there is no partial dependence, it is definitely 2nf. However, this relationship has a large amount of redundancy, and several attributes of the student are located, DNAME, Location will also generate a case where the above examples will be generated when it is repeatedly stored, inserted, deleted, and modified.
Cause: There is a conversion dependence in the relationship. Since SNAME and DNO are dependent on SNO, DNAME and Location are dependent on DNO, that is, through a student number can know the name of the student and his system code, but cannot be known from the school number, the school number and the system address is The system of the student corresponding to the school number is associated, so the keyword SNO is implemented by the Location function is implemented by the DNO -> Location. That is, the SNO does not directly determine non-primary property Location.
Solution: There is no transfer dependence in each relational mode.
Solution: Divided into two relations S (Sno, Sname, DNO), D (DNO, DNAME, LOCATION)
Note: There is no keyword DNO in the relational s. Otherwise, there is a loss between the two relationships.
In the design of the database, the database is often designed according to the third paradigm, and of course, in some cases to optimize the performance of the database, the relevant redundant fields are added to make the structure of the table do not meet 3nf, in most occasions, no items Complete results set for customers. This needs to select the data you need by connecting multiple sheets that have multiple dependencies in logically.
Before using Join, it must be understood that the data is retrieved from the relevant table based on the logical relationship between the individual tables. By SQL Server comes with a help file, you can clearly know: You can specify a join in the from or WHERE clause.
1.1. Specify the join in the WHERE clause
The following example uses the WHERE clause to do between the table
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 '600%'
Table A and Table B are coupled to Table B in the previous example, and the filter critcate is not less than A. ListDate by A.SYMBOL = B.SYMBOL.
In the WHERE clause, the join is specified, for a simpler join, using this way can be more convenient, but in combination, it is not recommended to use this syntax connection table.
1.2. Specify the join in the FROM clause
Take the above example to say, Table SECURITYCODE, the basic information of the securities code is mainly stored. Symbol represents the stock code, SNAME represents the stock name, ListDate represents the listing date; Table Dayquote mainly stores stocks in DayQuote. Symbol represents the stock code, TDATE Representative Trading Date, and Close represents the closing price. My current value logic is: Take out the stock code with 600 heads of stocks since all trading days since the listing date. Connect SecurityCode with Dayquote through Symbol.
Select A.SYMBOL, A.SNAME, B.TDATE, B. CLOSE
From securitycode a
Join Dayquote B
ON A.SYMBOL = B.SYMBOL
Where b.tdate> = a.listdate
And A.SYMBOL LIKE '600%'
Order by a.symbol, b.tdate
For the use of from clauses, you can clearly see the coupling conditions between the tables. There is a big advantage over readability and subsequent modification with the WHERE clause.
The following will be specifically introduced in the help file in connection with the connection, and the join can be divided into the following:
1.3. Inline
Inner connected (typical coupling operation, comparative operators like = or <>). Includes equally coupled and natural coupling.
The inner connection uses the comparison operator to match the values of the columns in each table.
In the SQL-92 standard, the inner linkage can be specified in the from or WHERE clause. This is the only type of SQL-92 support in the WHERE clause. The inner connection specified in the WHERE clause is called the old-style connection.
Inner connected general writing:
Select a.column1, [a.column2], b.column1, [b.column2]
From table1 a
[Inner] Join Table2 B
ON A.COLUMN0 = B.COLUMN0
INNER is omitted when using Inner Join in the query analyzer.
1.4. Outer joint
The outer joint may be left outwardly, and the right outwardly or completely external connection.
When the external joint is specified in the FROM clause, you can specify a set of groups in the following groups:
Left join or left outer join
The result set of left outwardly links includes all rows of the left table specified in the Left Outer clause, not just the lines matching the joint column. If a row of the left table does not match the line in the right, all the selected list columns of the right table in the associated result collection are null.
Right Join or Right Outer Join
Right outwardly is the reverse connection of left abutment. Will return all rows of the right table. If a row of the right table is not matched in the left table, the null value will be returned for the left table.
Full Join or Full Outer Join
Complete external join returns all rows in the left table and the right table. When a row does not match the line in another table, the selection list column of another table contains a null value. If there is a match between the table, the entire result is a list of data values for the base table.
The inner linkage returns only when there is at least one of the row of lines belonging to the two tables. Inner connected eliminates the rows that do not match the other table. The outer joint returns all the lines of at least one table or view mentioned in the FROM clause, as long as these rows meet any WHERE or HAVING search criteria. All rows of the left table referenced by the left outward joint reference will be retrieved, and all rows of the right to the right of the right outwardly. All rows of the two tables in the complete external join will return.
The common in the outer joint is Left Join, and the Left Join can solve most of the problems.
General Writing of Outer Containment:
Select a.column1, [a.column2], b.column1, [b.column2] from table1 a
LEFT | Right | Full [Outer] Join Table2 B
ON A.COLUMN0 = B.COLUMN0
Outer is often omitted when using Outer Join in the query analyzer. LEFT and RIGHT are just a directional issue. Under certain occasions, Full Outer Join is equivalent to the collection of resilience of Left Outer Join and Right Outer Join.
1.5. Cross connection
The cross-join returns all rows in the left table, and each line in the left table combines all rows in the right table. Cross joints are also known as Cartesi.
The cross joint without a WHERE clause will generate the designer of the table involved. The number of rows of the first table multiplied by the second table is equal to the size of the Descartes. That is to say, if there is no WHERE clause, if the table A has 3 line records, Table B has 6 lines of records ::
Select a. *, B. * From the table a cross Join table B
The above statement will return 18 line records.
