SQL INNER JOIN two-table joint query usage

Source: Internet
Author: User

Inner join (equivalent JOIN) returns only rows with equal JOIN fields in two tables.
If at least one match exists in the table, the inner join keyword returns the row.

Inner join keyword syntax

The code is as follows: Copy code
SELECT column_name (s)
FROM table_name1
Inner join table_name2
ON table_name1.column_name = table_name2.column_name

Note: inner join is the same as JOIN.


Left join returns records that include all records in the LEFT table and JOIN fields in the right table.

The left join keyword returns all rows from the LEFT table (table_name1), even if no matching row exists in the right table (table_name2.

Left join keyword syntax

The code is as follows: Copy code
SELECT column_name (s)
FROM table_name1
Left join table_name2
ON table_name1.column_name = table_name2.column_name

Note: in some databases, left join is called left outer join.

 

Right join returns records that include all records in the RIGHT table and the joined fields in the left table.

The right join keyword returns all rows from the RIGHT table (table_name2), even if no matching row exists in the left table (table_name1.

Right join keyword syntax

The code is as follows: Copy code
SELECT column_name (s)
FROM table_name1
Right join table_name2
ON table_name1.column_name = table_name2.column_name

Note: in some databases, right join is called right outer join.


Inner join syntax:

Use inner join to JOIN two data tables:

The code is as follows: Copy code
SELECT * FROM Table 1 inner join Table 2 ON Table 1. Field number = Table 2. Field number

Use inner join to JOIN three data tables:

The code is as follows: Copy code
SELECT * FROM (Table 1 inner join Table 2 ON Table 1. Field number = Table 2. Field number) inner join Table 3 ON Table 1. Field number = Table 3. Field number

Use inner join to JOIN four data tables:

The code is as follows: Copy code
SELECT * FROM (Table 1 inner join Table 2 ON Table 1. field Number = Table 2. field Number) inner join Table 3 ON Table 1. field Number = Table 3. field Number) inner join Table 4 ON Member. field Number = Table 4. field Number

Use inner join to JOIN five data tables:

The code is as follows: Copy code
SELECT * FROM (Table 1 inner join Table 2 ON Table 1. field Number = Table 2. field Number) inner join Table 3 ON Table 1. field Number = Table 3. field Number) inner join Table 4 ON Member. field Number = Table 4. field Number) inner join table 5 ON Member. field Number = Table 5. field Number
The code is as follows: Copy code
SELECT tx_txurheber_urheber.uid, tx_txurheber_urheber.werkxurhadrd_kurzname, tx_vda_werke.d_WerksArt
FROM tx_txurheber_urheber
Inner join tx_vda_werke ON tx_txurheber_urheber.fk_werk = tx_vda_werke. _ pk_Werk
AND tx_vda_werke.d_WerksArt = 'Theater'
LIMIT 0, 30

The usage of connecting the six data tables is as follows :)

Note:

• When entering letters, you must use English punctuation marks and leave a half-width space between words;
• When creating a data table, if a table is connected to multiple tables, the fields in this table must be of the "number" data type, and the same fields in multiple tables must be primary keys, the data type is "auto-numbered. Otherwise, it is difficult to connect successfully.

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.