Shulou(Shulou.com)06/01 Report--

JOINS type and its syntax

Natural joins (Natural connection):

-NATURAL JOIN clause

-USING clause

-ON clause

Self-connection

Non-equivalent connection

Outer joins (external connection):

-LEFT OUTER JOIN (left outer link)

-RIGHT OUTER JOIN (right external connection)

-FULL OUTER JOIN (full external connection)

Cartesian product

-Cross join (cross connect)

Syntax:

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]

Limit duplicate column names

Use table prefixes to restrict column names in multiple tables

Using table prefixes can improve efficiency

Use table aliases instead of full table name prefixes

The table alias provides a shorter name:

-less SQL code and less memory

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

Create a natural connection

The NATURAL JOIN clause, which creates an equivalent join with column conditions with the same name in two tables.

Query the table for data that meets the equivalence condition.

An error occurs if only the column name is the same but the data type is different.

If multiple column names match, it will be taken as a condition.

1. Query in which cities department_id and department_name are located.

Select department_id,department_name, location_id,city from departments natural join locations

Create a connection using the USING clause

If multiple columns have the same name, but the data types of natural connections do not match, you can use the using clause to specify that an equivalent column is used.

When multiple columns match, use the using clause to match the unique column

NATURAL JOIN and USING clauses are mutually exclusive

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 to specify the key value

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

The ON clause creates a connection

A natural connection is listed as a connection condition with the same name

Use the ON clause to specify any condition to join or to specify a column join condition

This connection condition is separate from other conditions.

The ON clause makes the statement more readable

1. Find the employee information in the two tables of employees and departments, and follow the condition of department_id

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)

Use AND clause or WHERE clause to apply additional conditions: query manager_id 149s

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,

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,

Use the ON clause to self-connect

Use the EMPLOYEE_ID of the conditional WORKER table MANAGER_ID equals MANAGER

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 external connections to return records that do not have a direct match

In SQL:1999, two table joins that return only matching rows are called inner joins.

During the join process, the two tables return not only the rows that meet the join criteria, but also the rows in the left (or right) table that do not meet the conditions, which is called a left (or right) outer join.

During the join process, the two tables return not only the rows that meet the join conditions, but also the rows in the two tables that do not meet the conditions, which is called a full outer join.

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

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)

Full external 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)

Cartesian product

Cartesian assemblies occur under the following conditions:

-the connection condition is omitted

-incorrect connection condition

-all rows in all tables are connected to each other

To avoid Cartesian sets, you can add valid join conditions to WHERE.

Create a cross connection

Use the CROSS JOIN clause to produce a cross set of joined tables.

A cross set is also called the Cartesian product between two tables.

Select last_name, department_name from employees cross join departments

Exercise:

1. Write a query for the HR department that requires the result to generate the addresses of all parts. Please use LOCATIONS and COUNTRIES tables

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

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

2. HR department needs a query statement that can find out the last_name,department_id,department_name of all employees.

Select last_name,department_id,department_name from employees join departments using (department_id)

3. Please find out the last_name,job_id,department_id,department_name of all employees who work at Toronto.

Select e.last_name,e.job_id,e.department_id,d.department_name from employees e join departments d

On (e.department_id = d.department_id)

Join locations l

On (d.location_id = l.location_id)

Where l.city = 'Toronto'

If Toronto is lowercase toronto, you can use the following statement

Select e.last_name,e.job_id,e.department_id,d.department_name from employees e join departments d

On (e.department_id = d.department_id)

Join locations l

On (d.location_id = l.location_id)

Where lower (l.city) = 'toronto'

4. Please find out the last_name,employee_id of each employee and the last_name,manager_id of the manager. Please order this in turn.

These columns have appropriate aliases of "Emeployee", "EMP#", "Manager" and "Mgr#".

Select w.last_name "Employee", w.employee_id "EMP#"

M.last_name "Manager", m.employee_id "Mgr#"

From employees w join employees m

On (w.manager_id = m.employee_id)

5. Show all employees (including King without manager). Please sort them by employee number.

Select w.last_name "employee", w.employee_id "emp#"

M.last_name "manager", m.employee_id "mgr#"

From employees w

Left outer join employees m

On (w.manager_id = m.employee_id)

Order by 2

6. Please find out the department_id of each employee, the last_name of each employee, and the colleagues who work together in the same department

Select e.department_id department,e.last_name employee

C.last_name colleague

From employees e join employees c

On (e.department_id = c.department_id)

Where e.employee_id c.employee_id

Order by e.department_id,e.last_name,c.last_name

7. Check the last_name,job_id,department_name,salary,grade_level of all the staff.

Select e.last_name,e.job_id,d.department_name

E.salary,j.grade_level

From employees e join departments d

On (e.department_id = d.department_id)

Join job_grades j

On (e.salary between j.lowest_sal and j.highest_sal)

8. Colleagues in the HR department want to know all the employees who have been hired after Davies. Please help write a SQL to find out the last_name and hire_date of these employees.

Select e.last_name,e.hire_date

From employees e join employees davies

On (davies.last_name='Davies')

Where davies.hire_date < e.hire_date

9. Colleagues in the HR department want to find out the last_name,hire_date of employees who were hired before their managers, as well as them.

Manager's last_name, and hire_date

Select w.last_name,w.hire_date,m.last_name,m.hire_date

From employees w join employees m

On (w.manager_id=m.employee_id)

Where w.hire_date < m.hire_date

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.