Full use of MySQL JOIN

  • 2020-05-09 19:25:51
  • OfStack

Outer join. An outer join may be a left outer join, a right outer join, or a full outer join.
When an outer join is specified in the FROM clause, it can be specified by one of the following groups of keywords:

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

Is the row that the join column matches. If a row in the left table has no matching rows in the right table, it is in the associated result set row

All select list columns in the right table are null.

RIGHT JOIN or RIGHT OUTER JOIN.
A right - out connection is the reverse connection of a left - out connection. All rows of the right table are returned. If a row in the right table is in the left table

A null value is returned for the left table if there is no matching row in.

FULL JOIN or FULL OUTER JOIN.
The complete outer join returns all rows in the left and right tables. When a row does not match a row in another table

The select list columns of 1 table contain null values. If there are matching rows between tables, the entire result set row contains the data for the base table

Value.

An inner join returns a row only if at least one row belonging to both tables meets the join criteria. Inner join removed with another

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

All the rows of the view, as long as they meet any WHERE or HAVING search criteria. Retrieves the tong

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

All rows of both tables in a partial join are returned.

Microsoft & reg; SQL Server & # 8482; 2000 pairs outside of the specified in the FROM clause

Join using 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 SQL-92 outer join syntax and is used in WHERE clauses

The *= and =* operators specify an old-style syntax for outer joins. The syntax of SQL-92 is not easily dissimilar

While the old Transact-SQL outer connection is sometimes ambiguous, SQL-92 is recommended

Method.

Use the left out join
Suppose you join the authors table and the publishers table on the city column. The results are shown only in

Authors in the city where the publisher lives (Abraham Bennet and Cheryl Carson in this case)

).

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

Left out connection. The following are the queries and 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

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

(23 row(s) affected)

LEFT OUTER JOIN whether or not it matches the city column in the publishers table

All rows of the authors table are included in the result. Note: most of the authors listed in the results are unrelated

The matched data, therefore, the pub_name column of these rows contains null values.

Use the right out join
Suppose you join the authors table and the publishers table on the city column. The results are shown only in

Authors in the city where the publisher lives (Abraham Bennet and Cheryl Carson in this case)

). SQL-92 right - out join operator RIGHT OUTER JOIN specifies: regardless of the first table

If there is a match, 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 lives in the city, use

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

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, you can restrict outer joins by one step. The following example contains the same right - handed outreach

Take, but eliminate the titles of books that sell less than 50 copies:

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

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 75 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!

(18 row(s) affected)

For more information about predicates, see WHERE.

Use a full outer join
To reserve the mismatch information by including the mismatched rows in the join result, use the full outer join.

Microsoft & reg; SQL Server & # 8482; 2000 provides the complete external join operator FULL

OUTER JOIN, this operator includes all the rows in both tables, regardless of whether the other table has a matching value.



Assume that the authors table and the publishers table are joined on the city column. The results are shown only in

Authors in the city where the publisher lives (Abraham Bennet and Cheryl Carson in this case)

). The SQL-92 FULL OUTER JOIN operator specifies: whether or not there is a matching data in the table, the node

The result will include all the rows in both tables.

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

In the same city, please use the full external connection. Below is a query for the Transact-SQL complete outer join

And the results to:

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

(30 row(s) affected)

Related articles: