When you
need to display fields from multiple tables at the same time, you can use table joins to implement such a feature.
- From the big class above, the table joins into the inner and outer joins, the most important difference between them is that the internal connection only selects the records that match each other in two tables, and the outer joins will select other mismatched records. What we use most is the inner connection.
- For example: query out the name of all employees and the department name, because employee names and departments are stored in the table EMP and dept, so you need to use a table connection to query:
- Select Ename,deptname from emp,dept where Emp.deptno=dept.deptno; Ename is the field in the table EMP Deptname is the field condition in the table dept indicates that the table EMP and the table dept deptno the same record concatenated into a record, from which the ename and Deptname fields are detected
- The outer joins are divided into left and right connections, which are defined as follows: Left JOIN: Contains all the records in the left table and even the right link in the right table that does not match it: contains all the records in the right table and even the records that do not match it in the left table
- Left join: Select Ename,deptname from the EMP left JOIN dept on Emp.deptno=dept.deptno, right connection: Select Ename,deptname from dept R join EMP on Dept.deptno=emp.deptno;
- These two look exactly the same. records that contain all of the records in the EMP table, even those that don't match it in the Dept table.
- In some cases, when we query, the condition is the result of the other SELECT statement, this time, we need to use the subquery
- The keywords used for subqueries mainly include in, not in, =,! =, exists, not exists, and so on.
- SELECT * from EMP where deptno in (select Deptno from dept);
If the subquery record number is unique, you can also use = instead of In:
SELECT * from emp where deptno = (select Deptno from dept); ERROR 1242 (21000): subquery returns more than 1 row
SELECT * from emp where deptno = (select Deptno from dept limit 1); Ok
- In some cases, subqueries can be converted to table joins, for example: SELECT * from EMP where deptno in (select Deptno from dept); Select emp.* from EMP, dept where Emp.deptno=dept.deptno;
Note: The conversion between subqueries and table joins is primarily applied in two ways: MySQL 4.1 does not support subqueries, and table joins are used to implement the function table connection of subqueries in many cases to optimize subqueries
We often encounter such an application, the two table data according to a certain query criteria, the results are merged together to display, this time, it is necessary to use the Union and the Union ALL keyword to implement such a function, the specific syntax is as follows: SELECT * from T1
union| UNION All
SELECT * from T2 ...
union| UNION All
SELECT * from TN;
The main difference between union and union all is that union all merges the result set directly, and the Union is distinct the result of the Union all once, removing the result of the duplicate record.
For example, the collection of department numbers in the EMP and dept tables is displayed:
-
< EM id= "__mcedel" > select deptno from EMP
-
< EM id= "__mcedel" > -> UNION ALL
Select Deptno from dept;
MySQL Table connection
