Oracle's left and right connection statement __oracle

The first oracle that came across the job that distinguishes it from SQL Server is Oracle's left-right connection statement.

This thing, or give a chestnut to describe the image of comparison. A user table users (user_id,address), Mobile number table Msisdns (USER_ID,MSISDN). For each user, the user table has a corresponding ID, but not every user has a cell phone number, therefore, the mobile phone number table does not necessarily have records. Let's look at the following statement first.

Select U.address, M.MSISDN

From users u, Msisdns m

where u.user_id = m.user_id

;

Obviously, we are looking for the user's address and cell phone number, but here we have added a limit (u.user_id = m.user_id), what will happen. Like what:

Users

user_id	Address
1	North
2	Southern

Msisdns

user_id	Msisdn
1	1580601****
Null	Null

So, after we execute the statement, we get only one record.

North District, 1580601****

However, this will sometimes not meet our requirements, we need the address of each user and mobile phone number, even if there is no cell phone number, you must show the address. Well, what do we do? Next, we transform the statement.

Select U.address, M.MSISDN

From users u, Msisdns m

where u.user_id = m.user_id (+)

;

The user_id in the Msisdns table is added (+), which is called the left connection. Let's see the results.

North District, 1580601****

South, NULL

Well, that's the result we wanted, the user in the south, no cell phone number, but we still got his address.

Let's analyze, the left connection, to be exact, is to associate the right table with the left table, the right table does not exist, the left table exists, the value of the left table is default to the right table of the empty record to associate. Conversely, the right connection (u.user_id (+) = m.user_id), that is, the left table exists with the Right table association, the left table does not exist, the right table exists in the case, the value of the right table is the default and left table of the empty record to associate. May be a little mixed up, let's translate the left connection statement.

Select U.address, (select M.msisdn from Msisdns m where u.user_id = m.user_id)

From Users U

;

As you can see, the address records for each of the users table will be lost, and then according to the user_id of users, to find the corresponding user_id in the Msisdns table, if not, it will be null output.

South, NULL

Therefore, the left join is to keep the left table, and as far as possible associated with the right table, there is no corresponding record, NULL instead. Right connection and vice versa.

Ps: We mentioned in this chapter of NULL, the essence of the output in the database is empty, that is, empty records, here in the image represented by null. Here, thank Prince Ye Lzw's technical support.