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

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