SQL JOIN example

Source: Internet
Author: User

Assume that the following table is used:

One is the voting master table, and the other is the voter information table ~ Record the IP address of the voter and the corresponding voting type. The left-right connection is actually the result of our joint query. Which table prevails ~
1: for example, right join or right outer join:

Take the voter table on the right as the standard. The record in the left table (voteMaster) is displayed only when its ID exists in the right table (voter). For example, ID 3.4.5.6 on the left is not displayed because the IDS do not have corresponding records in the right table!
2: therefore, we can naturally understand left join or left outer join.

It can be seen that the ID on the right is displayed only when it exists. If there is no corresponding data on the right, use NULL instead!
3: full join or full outer join. Data in both tables is displayed. The effect is the same as that in the previous example!
4: inner join or join. It is the record of the returned field ID in the table voteMaster and voter at the same time.

5: cross join (full join) cross join without the where Condition
A cross join without a WHERE clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. (Table 1 and Table 2 generate 6*3 = 18 Records)

Equivalent to select vm. id, vm. voteTitle, vt. ip from voteMaster as vm, voter as vt

6: Self-connection. Here I used an example from a previous power project (transformed)
See the following table:

This is a department table that stores departments and their upper-level departments, but all of them are placed in the same table. We assume that you need to use SQL to query the departments and their upper-level departments! How to do it,
Of course, the same can be done without self-connection:

We have achieved the expected goal! In this query, A subquery is used to query the names of the upper-level departments. If you use a self-connection, the structure is much clearer.

Is the function also completed? In addition to the self-connection, the left connection is also used, because the provincial power has no upper-level department, and he is the boss. If the internal connection is used, this record is filtered out because there is no higher-level department that matches the record.
Self-connection uses a lot of queries on the weight structure! Similar to the above table!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.