Full usage of MySQLJOIN: bitsCN.com 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.
Is the row matched by the join column. If a row in the left table does not match a row in the right table
All columns in the right table 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 is in the left table
If no matching row exists, 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. When a row does not match in another table
The selection list column of a table contains null values. If there are matched rows between tables, the entire result set row contains the data of the base table.
Value.
Rows are returned only when at least one row in the same two tables meets the join conditions. Internal join elimination and other
Any row in a table does not match. The outer join will return at least one table or
All rows in the view, as long as these rows meet any WHERE or HAVING search conditions. Retrieve
All rows in the left table referenced from the left outer join and all rows in the right table referenced by the right outer join. Complete
All rows of the two tables in the join operation are returned.
Microsoft®SQL Server™2000 pairs specified in the FROM clause
Join uses the following SQL-92 Keywords:
Left outer join or LEFT JOIN
Right outer join or RIGHT JOIN
Full outer join or FULL JOIN
SQL Server supports the outer join syntax for the SQL-92 and the use
* = And = * operator specifies the old syntax of outer join. Since SQL-92 syntax is not prone to discrimination
Meaning, while the old Transact-SQL outer join sometimes produces ambiguity, so it is recommended to use the SQL-92 language
Method.
Use left outer join
Assume that the authors table and the publishers table are joined in the city column. The result is only displayed in
Author of the publisher's City (in this example, Abraham Bennet and Cheryl Carson)
).
To include all the authors in the results, regardless of whether the publisher lives in the same city, use SQL-
92 left outer join. The following are the query results of the left outer join of Transact-SQL:
USE pubs
SELECT a. au_fname, a. au_lname, p. pub_name
FROM authors a left outer join publishers p
ON a. city = p. city
Order by p. pub_name ASC, a. au_lname ASC, a. au_fname ASC
Reginald Blotchet-Hils NULL
Micel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Heather McBadden NULL
Michael O 'Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
(23 row (s) affected)
Whether or not it matches the city column in the publishers table, LEFT OUTER JOIN
All rows of the authors table are included in the result. Note: most of the authors listed in the results do not have phase
Therefore, the pub_name column of these rows contains null values.
Use right outer join
Assume that the authors table and the publishers table are joined in the city column. The result is only displayed in
Author of the publisher's City (in this example, Abraham Bennet and Cheryl Carson)
). The SQL-92 right outer join operator right outer join specifies that no matter the first table
If any matched data exists, the result will contain all rows in the second table.
Use
The SQL-92 is connected right outward. The following are the queries and results of the right outer join of Transact-SQL:
USE pubs
SELECT a. au_fname, a. au_lname, p. pub_name
FROM authors AS a right outer join publishers AS p
ON a. city = p. city
Order by p. pub_name ASC, a. au_lname ASC, a. au_fname ASC
The following is the result set:
Au_fname au_lname pub_name
----------------------------------------------------------------
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
NULL Binnet & Hardley
NULL Five Lakes Publishing
Null ggg & G
NULL Lucerne Publishing
NULL New Moon Books
NULL Ramona Publishers
NULL Scootney Books
(9 row (s) affected)
Using predicates (such as comparing joins with constants) can further limit outer joins. The following example contains the same right outer
Answer, but eliminate the title of books with sales less than 50:
USE pubs
SELECT s. stor_id, s. qty, t. title
FROM sales s right outer join titles t
ON s. title_id = t. title_id
AND s. qty> 50
Order by s. stor_id ASC
The following is the result set:
Stor_id qty title
-------------------------------------------------------------------
---
(Null) But Is It User Friendly?
(Null) Computer Phobic AND Non-Phobic Inpiduals:
Behavior
Variations
(Null) Cooking with Computers: Surreptitious Balance
Sheets
(Null) Emotional Security: A New Algorithm
(Null) partition Ty Years in Buckingham Palace Kitchens
7066 75 Is Anger the Enemy?
(Null) Life Without Fear
(Null) Net Etiquette
(Null) Onions, Leeks, and Garlic: Cooking Secrets
Of
Mediterranean
(Null) Prolonged Data Deprivation: Four Case Studies
(Null) Secrets of Silicon Valley
(Null) Silicon Valley Gastronomic Treats
(Null) Straight Talk About Computers
(Null) Sushi, Anyone?
(Null) The Busy Executive's Database Guide
(Null) The Gourmet Microwave
(Null) The Psychology of Computer Cooking
(Null) You Can Combat Computer Stress!
(18 row (s) affected)
For more information about predicates, see WHERE.
Use a complete external connection
To retain the unmatched information by including unmatched rows in the join results, use the complete external join.
Microsoft®SQL Server™2000 complete external join operator FULL
Outer join, regardless of whether the other table has matched values, this operator includes all rows in the two tables.
Assume that the authors table and the publishers table are joined in the city column. The result is only displayed in
Author of the publisher's City (in this example, Abraham Bennet and Cheryl Carson)
). The SQL-92 full outer join operator specifies that
The result will contain all rows in the two tables.
To include all authors and publishers in the results, whether or not there are publishers or publishers in the city
In the same city, use a complete external connection. The following is a query of the complete external join of Transact-SQL.
And result:
USE pubs
SELECT a. au_fname, a. au_lname, p. pub_name
FROM authors a full outer join publishers p
ON a. city = p. city
Order by p. pub_name ASC, a. au_lname ASC, a. au_fname ASC
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
