Outer join, self join and union.P417

We only know half about joins.We haven't seen it yet

Outline join: Returns a record without a matching record in the table.

Self-join: You can join the table itself.

Union: Query results can be combined.

=========================================================

When you want to clean up old data, consider using outer joins to find unwanted values.

Outlinks return all rows from one table with matching conditions from another table.

When using injoins, you compare rows from two tables, but the order of the tables does not affect.

First, review:

SEKECT g.girl, t.toy
FROM girls g
INNER JOIN toys t
ON g.toy_id = t.toy_id;

This is the column that takes the toy_id column out of two tables and appears in both tables.

======================================================

LEFT OUTER JOIN receives all rows from the left table and matches them with rows from the right table.Left outer joins are particularly useful when the left table has a one-to-many relationship with the right table.

In LEFT OUTER JOIN, after FROM, the table before the join is called the left table, and the table after the join is the right table.

Use the left outer join to find out what toys each girl has.

Girls are left tables, toys are right tables

SELECT g.girl, t.toy
FROM girls g
LEFT OUTER JOIN toys t
ON g.toy_id = t.toy_id;

===============================================================

Outlinks match multiple results:

Although there are no matching records in another table, you will still go to the data rows, which will be taken out when you match more than one record.

Right outer join:

As with left outer joins, except that they are compared with right and left tables:

SELECT g.girl, t.toy
FROM toys t
RIGHT OUTER JOIN girls g
ON g.toy_id = t.toy_id;

Switch left and right using only one word.

==========================================================

Utilize Outer Join: P429

Find the head of the clown

Now the database schema is as follows:

New tables can be created: listing the IDs of each clown and his leader.Below is a clown organization chart with the ID of each clown.

And a clown leader table:

Note that number 10 is the big BOSS, so his box is himself.

Location of the new table:

Take another look at the current database schema and see how the new tables fit into the schema:

In the lower left corner is the new table.

The new table has a one-to-one relationship, the primary key ID column, and a one-to-many relationship, the boss_id column.The primary and foreign keys for this table come from the clown_info table.

In fact, we only need to add a new column to the clown_info table to record the ID of each clown leader, which is the boss_id, as designed in the clown_id table.

The boss_id in the clown_box table is a foreign key, and when we add this column to clown_info, it is still a foreign key, although it is also in the same table.This is called a self-referencing foreign key.

A self-reference represents a key that references another column in the same table.

Join a table to itself:

List the name of each clown and the id of their leader:

SELECT name, boss_id FROM clown_info;

But we need the name of the clown and the name of the leader.

SELECT c1.name, c2.name AS boss
FROM clown_info1 c1
INNER JOIN clown_info2 c2
ON c1.boss_id = c2.id;

Name the second column boss here. It's not confusing.

==========================================================

Two identical tables have just appeared for joining, but in fact the normal specification database does not have the same table twice.

Instead, we used self-joining to simulate the effect of joining two tables.

SELECT c1.name, c2.name AS boss
FROM clown_info c1
INNER JOIN clown_info c2
ONc1.boss_id = c2.id;

Instead of using two tables, use clown_info twice.

Set alias C1 for the first time, C2 for the second time, and then inline join c1.boss_id and c2.id

A self-join queries a single table against two originally identical tables.

==========================================================

Another way to get to the contents of multiple tables:

Here are the three tables Greg mentioned:

There are currently three separate SELECT statements:

SELECT title FROM job_cu rrent;
SELECT title FROM job_desired;
SELECT title FROM job_listings;

The above queries are OK, but we would like to merge them.

=============================================================

Use UNION

UNION combines the query results of two or more tables into one table based on the columns we specified in SELECT.You can think of UNION's query results as "overlapping" each SELECT query result.

SELECT title FROM job_current
UNION
SELECT title FROM job_desired
UNION
SELECT title FROM job_listings;

Then we find that there are no duplicate records in the result set, but the positions are not in order, so we add ORDER BY:P437

SELECT title FROM job_current ORDER BY title
UNION
SELECT title FROM job_desired ORDER BY title
UNION
SELECT title FROM job_listings ORDER BY title;

========================================================

Limitations on the use of UNION

Database software may fail because colleagues use multiple ORDER BY

UNION can only accept one ORDER BY and must be at the end of the statement.

To do this, we need to make some changes:

SELECT title FROM job_current
UNION
SELECT title FROM job_desired
UNION
SELECT title FROM job_listings
ORDER BY title;

==========================================

UNION ALL

It works like UNION, but returns all the contents of the column:

SELECT title FROM job_current
UNION ALL
SELECT title FROM job_desired
UNION ALL
SELECT title FROM job_listings
ORDER BY title;

So far, UNION's are all columns of the same data type, and we may want to combine UNION's of different classes as well.

The data will try to convert to the Royal type, and if it cannot, the query will fail.

=========================================================

Create tables from a union

The data types returned by UNION are not easily distinguishable unless they are captured in some way.Use CREATE TABLE AS to capture UNION results.

CREATE TABLE AS receives the results from a SELECT query and makes the results into a table:

CREATE TABLE my_union AS
SELECT title FROM job_current UNION
SELECT title FROM job_desired
UNION SELECT title FROM job_listings;

===========================================================

INTERSECT and EXCEPT

Intersection and Difference

Similar to UNION, find the overlap of query results.

INTERSECT only returns columns that are in the first and second queries at the same time.

SELECT title FROM job_current
INTERSECT
SELECT title FROM job_desired;

EXCEPT returns columns that appear only in the first query, not in the second query.

SELECT title FROM job_current
EXCEPT
SELECT title FROM job_desired;

=============================================================================

It is now the turn to compare subqueries with joins.

As mentioned earlier, joins can do almost anything a subquery can do.

P444

For example, here is a subquery statement:

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURE JOIN my_contacts AS mc
WHERE jc.title IN (SELECT title FROM job_listings);

Now let's rewrite with an inline join:

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURE JOIN my_contacts AS mc
INNER JOIN job_listings jl
ON jc.title = jl.title;

The question is which on earth do we use?

===============================================

We've seen how subqueries become joins, and now we're looking at joins that become subqueries:

SELECT c1.name, c2.name AS boss
FROM clown_info c1
INNER JOIN clown_info c2
ON c1.boss_id = c2.id;

Overwrite associated subquery: P449

SELECT c1.name,
(SELECT name FROM clown_info
WHERE c1.boss_id = id) AS boss
FROM clown_info c1;