The inner linkage is a connection to the value of the column to be coupled with a comparison operator.
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.
The following Transact-SQL query is an example of the inner connection:
USE PUBS
SELECT *
From authors as a inner join public publishers asp
ON A.CITY = P.city
Order by a.au_lname DESC
This inner connection is called equal connection. It returns all columns in two tables, but only returns rows that have equal values in the join column.
The following is the result set:
AU_ID AU_LNAME AU_FNAME Phone Address City
------------------------ ----------- ---- --------
238-95-7766 Carson Cheryl 415 548-7723 589 Darwin ln. Berkeley
409-56-7008 Bennet Abraham 415 658-9932 6223 BATEMAN St. Berkeley
State Zip Contract Pub_ID Pub_name City State Country
----- ------------------------------- --- -------------
CA 94705 1 1389 Algodata Infosystems Berkeley CA USA
CA 94705 1 1389 Algodata Infosystems Berkeley CA USA
(2 row (s) affected)
In the result set, the city column appeared twice. Due to the meaning of the same information, it is possible to eliminate one of the two identical columns by changing the selection list. The results are called natural coupling. You can repose the previous Transact-SQL query to form a natural join. E.g:
USE PUBS
Select PuB_ID, P.Pub_Name, P.State, a. *
From Publishers P Inner Join Authors A
On P.city = a.city
Order by a.au_lname ASC, A.au_fname ASC
The following is the result set:
PUB_ID PUB_NAME STATE AU_ID AU_LNAME AU_FNAME
------ ---------------------------------------- ------ 1389 Algodata Infosystems CA 409-56-7008 Bennet Abraham
1389 Algodata Infosystems CA 238-95-7766 CARSON CHERYL
Phone Address City State Zip Contract
---------------------------------- ----- -------- -----
415 658-9932 6223 BATEMAN St. Berkeley CA 94705 1
415 548-7723 589 Darwin Ln. Berkeley CA 94705 1 (2 row (s) affected)
In this example, Publishers.city did not appear in the result.
Use operators other than the equal sign
You can also connect the values in two unequal columns. The operators and predicates used for inner connections are also available for non-equal connection. For more information on operators and predicates that are available in the connection, see Using Operators and WHEREs in the expression.
The following Transact-SQL example is a New Moon writer greater than (>) coupled, which can be used to find the NEW MOON writer behind Massachusetts (alphabetically arranged), MASSACHUSETTS is the location of New Moon Books.
USE PUBS
Select P.pub_name, p.state, a.au_lname, a.au_fname, a.state
From Publishers P Inner Join Authors A
ON A.State> P.State
Where puB_name = 'new moon books'
Order by au_lname ASC, AU_FNAME ASC
The following is the result set:
Pub_name state au_lname au_fname stat
----------------------------------- -----------------
New Moon Books Ma Blotchet-Halls Reginald OR
New Moon Books Ma del Castillo Innes Mi
New Moon Books Ma Gree Mene Morningstar TN
New Moon Books MA Panteley Sylvia MD
New Moon Books MA Ringer Albert UT
New Moon Books Ma Ringer Anne UT
(6 row (s) affected)
Use not equal integration
I rarely use unequal coupling (<>). Usually I don't wait for the connection with the self-contained connection. For example, you can use the following unequal Transact-SQL join and self-contained lookups that contain two or more cheap (below $ 15) books:
USE PUBS
Select Distinct T1.TYPE, T1.PRICE
From Titles T1 Inner Join Titles T2
On T1.TYPE = T2.TYPE
And t1.price <> t2.price
Where t1.price <$ 15 and T2.PRICE <$ 15
Description Expression Not Column_Name = Column_name is equivalent to expression column_name <> column_name.
In the following Transact-SQL example, use a combination of non-equal connection and self-contained combinations to find all rows in the Titleauthor table, there are two rows or multi-lines with the same Title_ID but Au_ID number (ie, there are many books) Author): USE PUBS
SELECT DISTINCT T1.AU_ID, T1.TITLE_ID
From Titleauthor T1 Inner Join TitleAuthor T2
On T1.TITLE_ID = T2.TITLE_ID
WHERE T1.AU_ID <> t2.au_id
ORDER by t1.au_id
The following is the result set:
AU_ID TITLE_ID
------------ --------
213-46-8915 bu1032
267-41-2394 bu1111
267-41-2394 TC7777
409-56-7008 bu1032
427-17-2319 PC8888
472-27-2349 TC7777
672-71-3249 TC7777
722-51-5454 mc3021
724-80-9391 bu1111
724-80-9391 PS1372
756-30-7391 PS1372
846-92-7186 PC8888
899-46-2035 mc3021
899-46-2035 ps2091
998-72-3567 PS2091
(15 row (s) affected)
