SQL query Statement learning, multi-table queries and subqueries, and connection queries

Source: Internet
Author: User
Tags aliases

One, cross-connection query

This kind of query is basically not used, because this query method is obtained by the product of two tables (Cartesian set)

The syntax is select * from a A, B;

  

Second, internal connection query, can effectively remove the Cartesian set phenomenon

Intra-connection queries fall into two categories:

Implicit internal Connection Select * from a a a Where condition

Implicit connection using aliases: SELECT * from A alias 1,b alias 2 where alias 1.xx= alias 2.xx;

Show inner Connection SELECT * from A inner join B on condition (inner can be omitted)

Show connections using aliases: SELECT * from A alias 1 inner join B alias 2 on alias 1.xx= alias 2.xx

Third, outer connection

There are two ways of outer connection, one is left outer connection, one is right outer connection

Left outer connection: SELECT * from A ieft outer join B on condition

Right OUTER join: SELECT * FROM A-R join B on condition

The left outer connection is the contents of the table on the left display all, then matches the right table, if the right table does not match, then the empty

The right outer connection is the contents of the table on the right, and then matches the table on the left, and if the table on the left does not match, the empty

Four, sub-query:

A subquery is a query, which is the result of a SELECT statement as part of another select syntax (query results, query conditions, tables, etc.)

Subqueries are useful for example: Query the company's highest-paid employees for more information

SELECT * from emp where Sal=max (SAL) This is wrong because the aggregation function can not be used in the condition, in order to solve this problem, can only be used subquery

select* from EMP where sal= (select Max (SAL) from EMP)

exists keywords

SELECT * from emp where exists (select Max (SAL) from EMP)

This means that if (select Max (SAL) from EMP) has the result then the SELECT * from EMP is executed, otherwise

A subquery appears after where it appears as a condition

A subquery appears after the From and is present as a table

As a table example select E.emono,e.ename from (SELECT * from where deptno=30) E

gave the table an alias E

As a condition there are several situations

Single-column: You can use =,>,<,>=,<=,!=

A multiline column (collection) can be

Single-row multi-column (object) is a row, like an object, what property has

Multiline multiple columns: Multiple rows and columns are always used as tables from behind

Single-row Example: SELECT * from emp where sal > (select AVG (SAL) from EMP)

Multiline Multiple List example: SELECT * from emp where Sal> all (select Sal from emp where deptno=10)

Single-line Multiple enumeration example: SELECT * from EMP where (job,deptno,sal) in (select Job,deptno,sal from emp where ename= ' Yintian positive '); (Query and Yan Tianjong work, job number, Wages of the person's work, job number, salary from the EMP table)

V. Summary

An inner join is the intersection of two tables

Left OUTER join is the left table plus two table intersection

Right outer join is right table plus two table intersection

  

SQL query Statement learning, multi-table queries and subqueries, and connection queries

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.