Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

SQL based use of subqueries to retrieve data (22)

2025-01-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

Multi-column subquery

Where (manager_id, department_id) in

Subquery

100 90

102 60

124 50

Each row of the main query is compared to a subquery with multiple rows and columns

Comparison of columns

A comparison of multiple columns, including subqueries, which can be:

Non-paired comparison

Pairwise comparison

Compare subquery with subquery

1. Display other employee information in the same department and with the same manager as the employee named "John"

Select employee_id, manager_id, department_id from empl_demo

Where (manager_id, department_id) IN

(select manager_id, department_id from empl_demo

Where first_name = 'John')

AND first_name 'John'

Non-paired comparison

1. Display the employee number, manager number and department number of the manager ID and department ID whose name is not "John"

Select employee_id, manager_id, department_id

From empl_demo

Where manager_id in

(select manager_id

From empl_demo

Where first_name = 'john')

And department_id in

(select department_id

From empl_demo

Where first_name = 'john')

And first_name 'john'

Scalar quantum query expression

A scalar quantum query is a subquery that returns a column from a row.

Scalar quantum queries can be used in the following situations:

-part of DECODE and CASE conditions and expressions

-in all clauses except the GROUP BY clause in SELECT

SET clause and WHERE clause of-UPDATE statement

Scalar quantum queries in CASE expressions:

Select employee_id, last_name, department_id

(case

When department_id =

(select department_id

From departments

Where location_id = 1800)

Then 'canada' else' usa' end) location

From employees

Scalar quantum query in the ORDER BY clause:

Select employee_id, last_name,department_id

From employees e

Order by (select department_name

From departments d

Where e.department_id = d.department_id)

Related subquery

Related subqueries are executed row after row, and each row of the main query executes a subquery

Use columns from the main query in a subquery

Select column1, column2,...

From table1 Outer_table

Where column1 operator

(selecT column1, column2

From table2

Where expr1 = Outer_table.expr2)

2. Find all employee information about who earns more than the average salary of their department.

Select last_name, salary, department_id

From employees outer_table

Where salary >

(selecT AVG (salary)

From employees inner_table

Where inner_table.department_id =

Outer_table.department_id)

3. Show which employees have changed their jobs at least twice

Select e.employee_id, last_name,e.job_id from employees e

Where 2

(select dept_avg

From avg_cost)

Order by department_name

Recursive WITH clause

Recursive WITH clause:

Enables formulation of recursive queries.

Creates query with a name, called the Recursive WITH element name

Contains two types of query blocks member: anchor and a recursive

Is ANSI-compatible

With reachable_from (source, destin, totalflighttime) as

(

Select source, destin, flight_time

From flights

Union all

Select incoming.source, outgoing.destin

Incoming.totalflighttime+outgoing.flight_time

From reachable_from incoming, flights outgoing

Where incoming.destin = outgoing.source

)

Select source, destin, totalflighttime

From reachable_from

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.

Views: 250

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report