SQL based multi-table query (10)

The JOINS type and its syntax

Natural joins (natural connection):

–natural JOIN clause

–using clause

–on clause

Self-connect

Non-equivalent connection

Outer joins (external connection):

–left OUTER join (left OUTER join)

–right OUTER join (right outer join)

–full OUTER join (full outer join)

Cartesian product

–cross Join (Cross Connect)

Grammar:

Select Table1.column, Table2.column

From table1

[Natural Join table2] |

[Join Table2 using (column_name)] |

[Join Table2

On (table1.column_name = table2.column_name)]|

[Left|right|full outer JOIN table2

On (table1.column_name = table2.column_name)]|

[Cross join Table2];

To limit duplicate column names

• Use table prefixes to restrict column names in multiple tables

• Use table prefixes to improve efficiency

• Use table aliases instead of full table name prefixes

• Table aliases provide a shorter name:

–sql less code, using less memory

• Columns with the same column names in different tables can be distinguished by aliases

Create a natural connection

• The NATURAL join clause creates an equivalent join with a column of the same name in two tables.

• Query the table for data that satisfies the equivalent condition.

• If only the column names are the same and the data types are different, an error is generated.

• If more than one column name matches, it will be a condition.

1, query department_id and department_name in which cities

Select Department_id,department_name, location_id,city from departments natural join locations;

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8E/80/wKiom1jCQSeiDUUKAADqn9Q_PnU147.jpg-wh_500x0-wm_ 3-wmp_4-s_1914886027.jpg "title=" qq20170310140033.jpg "alt=" Wkiom1jcqseiduukaadqn9q_pnu147.jpg-wh_50 "/>

Using a using clause to create a connection

• If more than one column has the same name, but the data type of the natural join does not match, you can use the Using clause to specify that an equivalent column

• Match a using clause to a unique column when more than one column matches

• NATURAL JOIN and USING clause mutex

• Do not add a table name prefix or alias to the selected column

1, query employee_id,last_name,location_id from the employee table, and use department_id for the specified key value

Select employee_id, last_name,location_id, department_id from employees join departments using (DEPARTMENT_ID);

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8E/80/wKiom1jCQ6ezSKT0AACy3tz5qG4007.jpg-wh_500x0-wm_ 3-wmp_4-s_3730911069.jpg "title=" qq20170310140033.jpg "alt=" Wkiom1jcq6ezskt0aacy3tz5qg4007.jpg-wh_50 "/>

On clause creating a connection

• In a natural connection, a connection condition is listed with the same name.

• Use the ON clause to specify that you want to connect any condition or specify a column join condition

• This connection condition is separate from other conditions.

• The ON clause makes the statement more readable

1, find employees and departments two sheets of staff information, and according to DEPARTMENT_ID as a condition

Select e.employee_id, E.last_name, e.department_id, d.department_id, d.location_id from Employees e joins departments D on (e.department_id = d.department_id);

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/8E/80/wKiom1jCRWbgARifAADdvxwGU-M720.jpg "title=" Qq20170310140033.jpg "alt=" Wkiom1jcrwbgarifaaddvxwgu-m720.jpg "/>

Use an AND clause or a WHERE clause to apply additional conditions: query manager_id of 149

Select e.employee_id, E.last_name, e.department_id,

D.DEPARTMENT_ID, d.location_id

From Employees e join Departments D

On (e.department_id = d.department_id)

and e.manager_id = 149;

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8E/81/wKiom1jCTcnyqJewAACY_UOfC7I115.jpg-wh_500x0-wm_ 3-wmp_4-s_475689096.jpg "title=" qq20170310140033.jpg "alt=" Wkiom1jctcnyqjewaacy_uofc7i115.jpg-wh_50 "/>

Or

Select e.employee_id, E.last_name, e.department_id,

D.DEPARTMENT_ID, d.location_id

From Employees e join Departments D

On (e.department_id = d.department_id)

where e.manager_id = 149;

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/8E/81/wKiom1jCTg3haIKgAACAJTCUsTI593.jpg-wh_500x0-wm_ 3-wmp_4-s_1553272336.jpg "title=" qq20170310140033.jpg "alt=" Wkiom1jctg3haikgaacajtcusti593.jpg-wh_50 "/>

Using the ON clause to self-connect

Use conditional WORKER table manager_id equals MANAGER's employee_id

Select Worker.last_name emp, Manager.last_name Mgr

From employees worker join employees manager

On (worker.manager_id = manager.employee_id);

Non-equivalent connection

Select E.last_name, E.salary, J.grade_level

From Employees e Join Job_grades J

On E.salary

Between J.lowest_sal and J.highest_sal;

Use an outer join to return records that do not have a direct match

• In sql:1999, two tables are concatenated, and only the matching rows are returned, called Inner joins.

• Two tables returns rows in the left (or right) table that do not meet the criteria in addition to the rows that meet the conditions of the join, which is called a left (or right) outer join.

• Two tables returns rows in two tables that do not meet the criteria in addition to the rows that satisfy the join condition, which is known as a full outer join, during the connection process.

Left outer connection

Select E.last_name, e.department_id, D.department_name

From employees e LEFT OUTER JOIN departments D

On (e.department_id = d.department_id) Order BY department_id Desc;

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8E/81/wKiom1jCUj2BnGz_AACwZ4t1kso612.jpg-wh_500x0-wm_ 3-wmp_4-s_3906570772.jpg "title=" qq20170310140033.jpg "alt=" Wkiom1jcuj2bngz_aacwz4t1kso612.jpg-wh_50 "/>

Right outer connection

Select E.last_name, e.department_id, D.department_name

From Employees e right OUTER join departments D

On (e.department_id = d.department_id);

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/8E/81/wKiom1jCUsahWmYTAACEOTuRFFQ919.jpg-wh_500x0-wm_ 3-wmp_4-s_334826832.jpg "title=" qq20170310140033.jpg "alt=" Wkiom1jcusahwmytaaceoturffq919.jpg-wh_50 "/>

Full outer connection

Select E.last_name, d.department_id, D.department_name

From Employees e full OUTER JOIN departments D

On (e.department_id = d.department_id);

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/8E/7F/wKioL1jCUxzClzW4AACWs8KvY9k181.jpg-wh_500x0-wm_ 3-wmp_4-s_3023221895.jpg "title=" qq20170310140033.jpg "alt=" Wkiol1jcuxzclzw4aacws8kvy9k181.jpg-wh_50 "/>

Cartesian product

• The Cartesian assembly is produced under the following conditions:

– Connection conditions are missing

– Incorrect connection conditions

– All rows in all tables are connected to each other

• To avoid a Cartesian set, a valid join condition can be added to the where.

Create a cross-connect

• Use the CROSS join clause to cause the joined table to produce a fork set.

• A fork set is also known as a Cartesian product between two tables.

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8E/81/wKiom1jCVDzRLJOoAADS90iK8u0212.jpg-wh_500x0-wm_ 3-wmp_4-s_2979008052.jpg "title=" qq20170310140033.jpg "alt=" Wkiom1jcvdzrljooaads90ik8u0212.jpg-wh_50 "/>

Select Last_Name, department_name from employees cross join departments;

Exercises:

1. Write a query statement for the HR department that requires the result to generate all parts of the address. Please use the LOCATIONS and countries tables,

Requires output location_id,street_address,city,state_province, as well as country. Use natural connections to obtain the required results

Select Location_id,street_address,city,state_province,country_id,country_name from locations natural join countries;

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8E/81/wKiom1jCWrXw_P3vAAFbC1fAqig674.jpg-wh_500x0-wm_ 3-wmp_4-s_1736090994.jpg "title=" qq20170310140033.jpg "alt=" Wkiom1jcwrxw_p3vaafbc1faqig674.jpg-wh_50 "/>

2. HR department needs a query that can find out all the employees ' Last_name,department_id,department_name

Select Last_name,department_id,department_name from Employees join departments using (DEPARTMENT_ID);

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8E/80/wKioL1jCXIWTQJUWAADRgSu2mZs618.jpg-wh_500x0-wm_ 3-wmp_4-s_3467054230.jpg "title=" qq20170310140033.jpg "alt=" Wkiol1jcxiwtqjuwaadrgsu2mzs618.jpg-wh_50 "/>

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1905165

SQL based multi-table query (10)