SQL multi-Table query statements and methods

The following uses an equivalent connection 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 repeating columns in the authors and Publishers tables in the select list (city and state)

：

　　
Select A.*,p.pub_id,p.pub_name,p.country
From authors as a INNER JOIN publishers as P
On a.city=p.city

Outside query

Select a.*,b.* from Luntan left JOIN usertable as B
On A.username=b.username

　　
The following uses a full outer join to all authors in the city table and all authors in the user table, as well as the cities in which they are located

：

　　
Select a.*,b.*
From city as a full OUTER JOIN user as B
On A.username=b.username

Cross-check

The cross join does not take a WHERE clause, which returns the Cartesian product of all data rows of the connected two tables, returned to the result set

The number of rows in the hop equals the number of rows in the first table that match the query criteria, multiplied by the criteria in the second table that match the query.

The number of data rows. For example, there are 6 categories of books in the titles table, and there are 8 publishers in the publishers table, the following cross joins

The number of records retrieved will be equal to the 6*8=48 line.

　　
Select Type,pub_name
From titles CROSS JOIN Publishers
ORDER BY Type

Connect Forum content to author information using left outer joins:

Select a.*,b.* from Luntan left JOIN usertable as B

On A.username=b.username

The following uses a full outer join to all authors in the city table and all authors in the user table, as well as the cities in which they are located

：

Select a.*,b.*

From city as a full OUTER JOIN user as B

On A.username=b.username

(iii) Cross-linking

The cross join does not take a WHERE clause, which returns the Cartesian product of all data rows of the connected two tables, returned to the result set

The number of rows in the hop equals the number of rows in the first table that match the query criteria, multiplied by the criteria in the second table that match the query.

The number of data rows.

For example, there are 6 categories of books in the titles table, and there are 8 publishers in the publishers table, and the following cross joins retrieve

The number of records will wait

On the 6*8=48 line.

Select Type,pub_name

From titles CROSS JOIN Publishers

ORDER BY Type

Let's take a look at a multiple-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 BY

Zgy_jobs_faces.jname ORDER BY Zgy_jobs_option.jid desc limit 0,30 ";

Filter duplicate data with GROUP by

Keywords:SQL query, multiple table query