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