Full usage of MySQL join _mysql

Outer Joins. An outer join can be a left outer join, a right outer join, or a full outer join.
When you specify an outer join in the FROM clause, you can specify one of the following groups of keywords:

Left JOIN or left OUTER join.
The result set of the left outer join consists of all the rows of the left table specified in the OUTER clause, not just

Is the row that the join column matches. If a row in the left table does not have a matching row in the right table, then in the associated result set row

All select list columns for the right table are null values.

Right join or right OUTER join.
A right outer join is a reverse join of a left outer join. All rows from the right table will be returned. If a row in the right table is on the left table

There is no matching row in the, the null value will be returned for left table.

Full join or full OUTER join.
A full outer join returns all rows in the left and right tables. When a row does not match rows in another table, the other

A table's select list column contains null values. If there is a matching row between the tables, the entire result set row contains data from the base table

Value.

An inner JOIN returns rows only if at least one row that belongs to the two tables conforms to the join condition. The inner join eliminates the other

A row in which any row in a table does not match. The outer join returns at least one table mentioned in the FROM clause or

All rows of the view, as long as the rows conform to any WHERE or having search conditions. Will retrieve the pass

All rows of the left table referenced by a LEFT outer join, and all rows of the right table referenced by a right outer join. Complete outside

All rows of the two tables in the part join are returned.

Microsoft®sql server™2000 to the outside specified in the FROM clause

Joins use 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 SQL-92 outer join syntax, and in the WHERE clause use

The *= and =* operators specify the old syntax for outer joins. Because SQL-92 grammar is not prone to ambiguity

, whereas old-style Transact-SQL outer joins are sometimes ambiguous, so it is recommended to use the SQL-92 language

Method.

Using a LEFT OUTER join
Suppose you join the authors table and the publishers table on the city column. Results are displayed only in

Author of the city where the publisher resides (in this case Abraham Bennet and Cheryl Carson

）。

To include all authors in the results, regardless of whether the publisher lives in the same city, use the sql-

92 left outer joins. The following are queries and results for Transact-SQL left-outward joins:

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

Here is the result set:

au_fname au_lname pub_name
-------------------- ------------------------------ -----------------


Reginald Blotchet-halls NULL
Michel 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

(Affected row (s))

Whether or not it matches the city column in the publishers table, the left OUTER JOIN

All rows of the authors table are included in the result. Note: Most of the authors listed in the results are not

Matching data, therefore, the pub_name columns of these rows contain null values.

Using the right outer join
Suppose you join the authors table and the publishers table on the city column. Results are displayed only in

Author of the city where the publisher resides (in this case Abraham Bennet and Cheryl Carson

）。 SQL-92 the right outer join operator right-hand OUTER join indicates that no matter the first table

If there is a matching data, the result will contain all the rows in the second table.

To include all publishers in the results, regardless of whether or not a publisher resides in the city, use

SQL-92 the right outer join. The following are queries and results of Transact-SQL right outer joins:

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

Here is the result set:

au_fname au_lname pub_name
-------------------- ------------------------ --------------------
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
Null NULL Binnet & Hardley
Null null Five Lakes Publishing
Null NULL GGG&G
Null NULL Lucerne Publishing
Null NULL NEW Moon Books
Null NULL Ramona Publishers
Null NULL Scootney Books

(9 row (s) affected)

Using predicates, such as comparing joins to constants, can further restrict outer joins. The following example contains the same right outreach

But to eliminate the title of a book that sells less than 50 copies:

Use pubs
SELECT s.stor_id, S.qty, T.title
From the sales s right OUTER JOIN titles T
On s.title_id = t.title_id
and S.qty > 50
ORDER BY s.stor_id ASC

Here is the result set:

STOR_ID Qty Title
------- ------ ------------------------------------------------------

---
(NULL) (NULL) But is It User friendly?
(NULL) (NULL) Computer phobic and Non-phobic individuals:

Behavior
Variations
(NULL) (NULL) Cooking with computers:surreptitious Balance

Sheets
(NULL) (NULL) Emotional security:a New algorithm
(NULL) (NULL) Fifty Years in Buckingham Palace Kitchens
7066 is anger the enemy?
(NULL) (NULL) Life without Fear
(NULL) (NULL) Net Etiquette
(NULL) (NULL) onions, leeks, and garlic:cooking secrets

of the
Mediterranean
(NULL) (NULL) Prolonged Data Deprivation:four Case studies


(NULL) (NULL) Secrets of Silicon Valley
(NULL) (NULL) Silicon Valley Gastronomic Treats
(NULL) (NULL) Straight Talk about Computers
(NULL) (NULL) Sushi, Anyone?
(NULL) (NULL) The Busy Executive ' s Database Guide
(NULL) (NULL) The Gourmet Microwave
(NULL) (NULL) The psychology of Computer cooking
(NULL) (NULL) You Can combat Computer stress!

(s) affected)

For more information about predicates, see WHERE.

Using full outer Joins
To preserve unmatched information by including mismatched rows in the join result, use a full outer join.

Microsoft®sql server™2000 provides full outer join operator

OUTER JOIN, this operator includes all rows in two tables, regardless of whether another table has a matching value.



Suppose you join the authors table and the publishers table on the city column. Results are displayed only in

Author of the city where the publisher resides (in this case Abraham Bennet and Cheryl Carson

）。 The SQL-92 full OUTER JOIN operator indicates that, regardless of whether there is a matching data in the table, the knot

The fruit will include all rows from the two tables.

To include all authors and publishers in the results, regardless of whether or not a publisher or publisher in the city lives

In the same city, use a full outer join. The following is a query for Transact-SQL full outer joins

and results:

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

Here is the result set:

au_fname au_lname pub_name
-------------------- ---------------------------- --------------------


Reginald Blotchet-halls NULL
Michel 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
Null NULL Binnet & Hardley
Null null Five Lakes Publishing
Null NULL GGG&G
Null NULL Lucerne Publishing
Null NULL NEW Moon Books
Null NULL Ramona Publishers
Null NULL Scootney Books

(s) affected)