Shulou(Shulou.com)05/31 Report--

How to carry out sql table join query analysis, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Connection query:

A join query is a query based on two or more tables or views. In practical applications, querying a single table may not satisfy the application.

The requirements of the program, in which case you need to use join queries

# when using join queries, you must make two or more tables after the from clause

# when using a join query, you should prefix the column names with an indication. However, if the column names of different tables are different, you can not add the prefix of the table name. If there are columns with the same name between different tables, you must prefix them.

# when making a join query, you can use the alias of the table to simplify the join query statement

1) Equal connection:

Equality query refers to a join query that uses a small lamp comparator (=) to specify connection conditions. This kind of join query mainly retrieves relevant data between master and slave tables.

Select table1.column table2.column from table1,table2 where table1.column1=table2.column2

2) unequal connection:

Unequal joins are join queries that use comparison operators other than equality comparators in join conditions; unequal joins are mainly used to display a specific range of information between different tables.

For example, select a. Ename. a where a.sal between b.losal and b.hisal. Grade from emp a.

3) self-connect:

Self-join refers to the query join between the same table, which is mainly used to show the superior-subordinate relationship or primary-secondary relationship in the self-reference table.

If there is such a table below

EMPNO ENAME MGR

7839 KING

7566 JONES 7839

7698 BLAKE 7839

7782 CLARK 7566

In this table, there are employee id, employee name, employee superior, such a table, there is a subordinate and subordinate relationship between rows, so self-join is used here.

Such as: select manager.ename From emp manager,emp worker where manager.empno=worker.mgr and worker.ename='BLAKE'

3) Internal connection and external connection:

The inner connection is used to return records that meet the conditions, while the outer connection is an extension of the inner connection. It returns not only all connections that meet the conditions, but also records that do not meet the connection conditions.

Syntax:

Select table1.column,table2.column2 from table1 [INNER | LEFT | RIGHT | FULL] join table2 on table1.column1=table2.column2

1 Inner join: the inner join returns all records that meet the join conditions. By default, these join queries belong to the inner join if no operator is established when executing the join query. In fact, the effect of the inner join is the same as that of the equivalent join. The reason why it is called the inner join is relative to the outer join!

Such as: select a.dname.b.ename from dept arecedence emp b where a.deptno=b.deptno and a.deptno=10

2 left (right) outer join: the left outer join is implemented with the LEFT [RIGHT] JOIN option. When using the left (right) outer join, not only all records of the user's connection conditions are returned, but also other rows of the left [right] side table of the join operators that do not meet the connection conditions are returned.

4) full external connection:

A full outer join is achieved through full JOIN. When a full outer join is used, not only all rows that meet the join conditions are returned, but also all other rows that do not meet the join conditions are returned.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.