SQL multi-Table query statements and Methods

Source: Internet
Author: User
There are many ways to query Multiple SQL tables, such as connecting INNERJOIN, outer query LEFTJOIN, cross query JOIN, cross JOIN, and other joinonlefton.

There are many ways to query Multiple SQL tables, such as joining INNER JOIN, LEFT JOIN in outer query, JOIN in cross query, JOIN in cross join, and JOIN on left on.

SQL multi-Table query statements and Methods

There are many SQL multi-table queries, such as joining INNER JOIN, LEFT JOIN and cross Query

JOIN, cross JOIN, and so on join on left on.

The following uses equijoin to list authors and publishers in the same city in the authors and publishers tables:

  
Select *
FROM authors AS a inner join publishers AS p
ON a. city = p. city

If you use a natural connection, delete the duplicate columns (city and state) in the authors and publishers tables in the selection list)

:

  
Select a. *, p. pub_id, p. pub_name, p. country
FROM authors AS a inner join publishers AS p
ON a. city = p. city

External Query

Select a. *, B. * FROM luntan left join usertable as B
ON a. username = B. username


  
Next we will use a full outer join to list all the authors in the city table, all the authors in the user table, and their cities.

:

  
Select a. *, B .*
FROM city as a full outer join user as B
ON a. username = B. username

Cross Query

The cross join clause does not contain the Where clause. It returns the Cartesian product of all data rows in the two joined tables and returns the result set.

The number of rows in the table is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table

The number of data rows. For example, if there are 6 types of books in the titles table, and there are 8 publishers in the publishers table

The number of retrieved records is 6*8 = 48 rows.

  
Select type, pub_name
FROM titles cross join publishers
Order by type


Use the left outer link to connect the Forum content with the author information:

Select a. *, B. * FROM luntan left join usertable as B

ON a. username = B. username

Next we will use a full outer join to list all the authors in the city table, all the authors in the user table, and their cities.

:

Select a. *, B .*

FROM city as a full outer join user as B

ON a. username = B. username


(3) cross join

The cross join clause does not contain the Where clause. It returns the Cartesian product of all data rows in the two joined tables and returns the result set.

The number of rows in the table is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table

The number of data rows.

For example, if there are 6 types of books in the titles table and 8 publishers in the publishers table

Number of records, etc.

Rows 6*8 = 48.

Select type, pub_name

FROM titles cross join publishers

OrDER BY type

Let's look at a multi-Table query I wrote.

$ SQL = "Select zgy_jobs_faces. *, zgy_jobs_index. *, zgy_jobs_option. * from

Zgy_jobs_faces, zgy_jobs_index, zgy_jobs_option where zgy_jobs_option.mulplace

= '$ City' and zgy_jobs_faces.djobskinds =' $ parttime' and zgy_jobs_faces.cid =

Zgy_jobs_option.cid and zgy_jobs_option.cid = zgy_jobs_index.cid group

Zgy_jobs_faces.jname order by zgy_jobs_option.jid desc limit 0, 30 ";

Use group by to filter duplicate data

Keywords: SQL query, multi-Table query

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.