Connection query outside of database connection query __ database

The actual database contains many tables, and it is not possible to query only one table in practical applications. If a query involves more than two tables, it is called a connection query. A connection query is the most important query in a relational database. The connection query includes equivalent connection query, non equivalence connection query, natural connection query, own connection query, outer join query and compound conditional connection query, etc.

Outer Joins
In a common connection operation, only tuples that meet the query criteria for a connection can be output as results. Because a connection query operates a field in a table that does not meet the criteria, it does not appear in the query result set, in practical applications often need to be the query to discard the field is also displayed in the result table, and not meet the conditions of the field with a null value to express

An outer join includes a left outer join, a right outer join, and a full join.

If you leave only the tuples you want to discard in the left table, it's called a left outer join . That is, all tuples on the left table are displayed in the result table, and the corresponding attributes in the right table are replaced with null values for tuples with no connections on the left table. In other words, two table above the connection query, query two tables are left table and right table, query in the left table to go to a part of the field as a result table field, also in the right table to take a part of the field as a result set field, when the connection conditions are met when the left and right table fields have corresponding values, do not meet the query conditions, The field in the left table does not find the corresponding field match in the right table, and the corresponding field in the right table is represented by a null value.

The connection result of the right outer join contains all the tuples in the right table, and the corresponding field in the left table is replaced with a null value for the tuple on the right table that does not have a connection.

The full outer Join result contains all the selected tuples in the left and right tables. For tuples with no connections on the left table, the corresponding attributes in the right table are replaced with null values, and the corresponding attributes in the left table are replaced with null values for the tuples in the right table that are not connected.

Parking available parking Space inquiries, there are parkingno parking management table, booking booking information table, both tables have parking_no (parking number) field, now need to query in the Parkingno appear in the booking table does not appear in the Parking_no field, It's a parking number that's not booked.

Parkingno Parking Management table

Booking Reservation Information Form

The result is a parking lot number outside A1001 and A1003.

First LEFT OUTER join query

Left OUTER JOIN query results

The result of achieving the target also needs a brush selection condition is to select the Parking_no field of the left table (Parkingno table) corresponding to the empty Parking_no field in the right table (booking table).

As follows

That is, on the basis of a left outer join, you can add a where, and so on.

Close SQL statements to achieve target results

For more details please refer to the database principle and Design (second edition) People's Posts and telecommunications publishing house 83-84 pages