Multi-table join queryThe connection between tables is divided into inner connection and outer connection
Inner Join First, prepare two tables Student table Score table Inner join: Find the common records that meet the conditions in each table. Query the student names and scores in the student table. The first way to write: only use where select a.s_name, b.s_score from student a,score b where a.s_id = b.s_id; The second way to write it: join ... on ... select a.s_name, b.s_score from student a join score b on a.s_id = b.s_id The third way to write it: inner join … on… select a.s_name, b.s_score from student a inner join score b on a.s_id = b.s_id Left JoinLeft join: According to the records in the left table, find the records that meet the conditions in the connected right table to match them. If no records matching the left table are found, null is used to represent them. The first way to write it: left join … on … select a.s_name,b.s_score from student a left join score b on a.s_id = b.s_id The second way of writing: left outer join … on … select a.s_name,b.s_score from student a left outer join score b on a.s_id = b.s_id Right Join**Right join: **According to the records in the right table, find the records that meet the conditions in the connected left table to match them. If no match is found, it is represented by null. The first way to write it: right join … on … select a.s_name,b.s_score from student a right join score b on a.s_id = b.s_id; The second way of writing: right outer join … on … select a.s_name,b.s_score from student a right outer join score b on a.s_id = b.s_id; SubqueriesSubquery : It is a way to implement multi-table join query. Another select statement is nested in the from clause or where clause of a select statement. The outer select query statement becomes the main query. In other words , the query statement in WHERE or FORM is called a subquery. Subquery in WHERE clause: The value returned by the subquery is used as the query condition of the main query Subquery in the FROM clause: The subquery returns a virtual table, and the main query finds the conditions that meet the conditions from the temporary table. SummarizeThis article ends here. I hope it can be helpful to you. I also hope that you can pay more attention to more content on 123WORDPRESS.COM! You may also be interested in:
|
<<: Linux operation and maintenance basics httpd static web page tutorial
>>: How to use CSS3 to implement a queue animation similar to online live broadcast
Recently, the company is preparing to develop an ...
Table of contents What does the COUNT function do...
Nginx optimization---hiding version number and we...
Table of contents 01 Common controllers in k8s RC...
The pop-up has nothing to do with whether your cur...
An application of CSS animation, with the same co...
1. What is deadlock? The official definition is a...
Many friends who have just started to make web pag...
The following command is often used: chmod 777 文件...
This article will not explain the use and install...
Installation suggestion : Try not to use .exe for...
I recently started learning database, and I feel ...
Unicode is a character encoding scheme developed ...
1. Background Generally, in a data warehouse envi...
Without going into details, let's go straight...