sql多表连接查询

Easter79
• 阅读 817

本文主要列举两张和三张表来讲述多表连接查询。
新建两张表:
表1:student  截图如下:
sql多表连接查询
表2:course  截图如下:
sql多表连接查询
(此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键。)
一、外连接
外连接可分为:左连接、右连接、完全外连接。
1、左连接  left join 或 left outer join
SQL语句:select * from student left join course on student.ID=course.ID
执行结果:
sql多表连接查询
左外连接包含left join左表所有行,如果左表中某行在右表没有匹配,则结果中对应行右表的部分全部为空(NULL).
注:此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数,因为左右两表此时为一对一关系。
2、右连接  right join 或 right outer join
SQL语句:select * from student right join course on student.ID=course.ID
执行结果:
sql多表连接查询
右外连接包含right join右表所有行,如果左表中某行在右表没有匹配,则结果中对应左表的部分全部为空(NULL)。
注:同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数,因为左右两表此时为一对一关系。
3、完全外连接  full join 或 full outer join
SQL语句:select * from student full join course on student.ID=course.ID
执行结果:
sql多表连接查询
完全外连接包含full join左右两表中所有的行,如果右表中某行在左表中没有匹配,则结果中对应行右表的部分全部为空(NULL),如果左表中某行在右表中没有匹配,则结果中对应行左表的部分全部为空(NULL)。
二、内连接  join 或 inner join
SQL语句:select * from student inner join course on student.ID=course.ID
执行结果:
sql多表连接查询
inner join 是比较运算符,只返回符合条件的行。
此时相当于:select * from student,course where student.ID=course.ID
三、交叉连接 cross join
1.概念:没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
SQL语句:select * from student cross join course
执行结果:
sql多表连接查询
如果我们在此时给这条SQL加上WHERE子句的时候比如SQL:select * from student cross join course where student.ID=course.ID
此时将返回符合条件的结果集,结果和inner join所示执行结果一样。
四、两表关系为一对多,多对一或多对多时的连接语句
当然上面两表为一对一关系,那么如果表A和表B为一对多、多对一或多对多的时候,我们又该如何写连接SQL语句呢?
其实两表一对多的SQL语句和一对一的SQL语句的写法都差不多,只是查询的结果不一样,当然两表也要略有改动。
比如表1的列可以改为:
Sno Name Cno
表2的列可以改为:
Cno CName
这样两表就可以写一对多和多对一的SQL语句了,写法和上面的一对一SQL语句一样。
下面介绍一下当两表为多对多的时候我们该如何建表以及些SQL语句。
新建三表:
表A:  student 截图如下:
sql多表连接查询
表B:  course 截图如下:
sql多表连接查询
表C:  student_course 截图如下:
sql多表连接查询
一个学生可以选择多门课程,一门课程可以被多个学生选择,因此学生表student和课程表course之间是多对多的关系。
当两表为多对多关系的时候,我们需要建立一个中间表student_course,中间表至少要有两表的主键,当然还可以有别的内容。
SQL语句:select s.Name,C.Cname from student_course as sc left join student as s on s.Sno=sc.Sno left join course as c on c.Cno=sc.Cno
执行结果:
sql多表连接查询
此条SQL执行的结果是学生选课的情况。

一、多表查询

    简言之,根据特定的连接条件从不同的表中获取所需的数据

    笛卡尔集的产生条件:

       省略连接条件

       连接条件无效

       第一个表中的所有行与第二个表中的所有行相连接

二、多表查询语法:*/

    SELECT table1.column, table2.column

    FROM table1, table2

    WHERE table1.column1 = table2.column2;

    /*

    但要注意where 不要省了,省略where 即为笛卡尔集,而且where 条件要有效,

    两张表间有一个相同的字段,才好进行有效的多表查询

    查询时列名前,加表名或表别名前辍(如果字段在两个表中是唯一的可以不加)

    为了简化SQL书写,可为表名定义别名,格式:from 表名别名

    如:from emp e,dept d

    建议使用表的别名及表前缀,使用表别名可以简化查询,而使用表前缀则可以提高查询性能

    例:查询每个员工的工号,姓名,工资,部门名和工作地点*/

    select empno,ename,sal,dname,loc from emp,dept

    where emp.deptno=dept.deptno;

/*

三、多表连接类型:

    从数据显示方式来讲有:内连接和外连接。

    内连接:只返回满足连接条件的数据。

    外连接:除了返回满足连接条的行以外,还返回左(右)表中,不满足条件的行,

       称为左(右)连接

    内连接*/

    select empno,ename,sal,dname,loc from emp,dept

    where emp.deptno=dept.deptno;  --(Oracle 8i 及以前的写法)

    --内连接的另一种写法:

    select empno,ename,job,sal,dept.deptno,dname,loc

    from emp join dept on (emp.deptno=dept.deptno); -- (SQL 99的写法)

    /*

    外连接:

    两个表的查询中,使用外连接可以查询另一个表或者两个中不满足连接条件的数据。

    外连接的符号是(+),(+)要放在字段名后。(+)对面的那个表,会全部显示。

    外连接语法*/

        SELECT table1.column, table2.column   --右外连接

       FROM table1, table2

       WHERE table1.column(+) = table2.column;

       SELECT table1.column, table2.column   --左外连接

       FROM table1, table2

       WHERE table1.column = table2.column(+);

    --例:

    select empno,ename,job,sal,dept.deptno,dname,loc

    from emp,dept

    where emp.deptno(+)=dept.deptno; -- (Oracle 8i 及以前的写法)

    --另一种写法(右连接):     -- (SQL 99的写法)

    select empno,ename,job,sal,dept.deptno,dname,loc

    from emp right join dept on (emp.deptno=dept.deptno);

    --左连接:                    (SQL 99的写法)

    select empno,ename,job,sal,dept.deptno,dname,loc

    from emp left join dept on (emp.deptno=dept.deptno);

    --全连接(满连接)            (SQL 99的写法)

   select empno,ename,job,sal,d.deptno,dname,loc

    from emp e full join dept d on (e.deptno=d.deptno);

    /*

    自然连接                    (SQL 99的写法)

    以两个表具有相同的字段的所有列为基础,可采用自然连接(natural join)

    它将选择两个表中那些在所有匹配的列中值相等的行。

    如果列具有相同的名称,但数据类型能够不同,则会报错*/

    select empno,ename,job,sal,deptno,dname,loc

    from emp natural join dept;

    /*

    自连接:

    将自身表的一个镜像当另一个表来对待。*/

    select ... from emp e,emp d  ...

    --下面列出SQL 99的语法供参考

    SELECT table1.column, table2.column

    FROM table1

    [CROSS JOIN table2] |

    [NATURAL JOIN table2] |

    [JOIN table2 USING (column_name)] |

    [JOIN table2

    ON(table1.column_name = table2.column_name)] |

    [LEFT|RIGHT|FULL OUTER JOIN table2

    ON (table1.column_name = table2.column_name)];

    --在下面的语法中

    table1.column     --指明从中检索数据的表和列

    CROSS JOIN        --返回两个表的笛卡尔集

    NATURAL JOIN      --根据相同的列名连接两个表

    JOIN table

    USING column_name --根据列名执行等值连接

    JOIN table ON

    table1.column_name   --根据ON 子句中的条件执行等值连接

    = table2.column_name

    LEFT/RIGHT/FULL OUTER

    /*

    使用using子句创建连接

        如果几个列具有相同的名称,但是数据类型不匹配,那么可以使用using子句来修改natural join

       子句以指定要用于等值连接的列

       在多个列匹配时,使用using 子句只匹配一个列

       在引用列中不要使用表名或别名

       natural join 和using 子句是互不相容的 */

        --例:

       SELECT l.city, d.department_name

       FROM locations l JOIN departments d USING (location_id)

       WHERE location_id = 1400;

       --下面的语句无效,因为where 子句中限定了location_id

       SELECT l.city, d.department_name

       FROM locations l JOIN departments d USING (location_id)

       WHERE d.location_id = 1400;

       ORA-25154: column part of USING clause cannot have qualifier

       --注意:

       --   两个表中名称相同的列在使用时不能有任何限定符,这一限制同样适用于natural join

/*

四、演示:      */ 

--笛卡尔集

    SQL> select empno,ename,dname from emp,dept;

        EMPNO ENAME      DNAME

    ---------- ---------- --------------

         7369 SMITH      ACCOUNTING

         7499 ALLEN      ACCOUNTING

         7521 WARD       ACCOUNTING

         7566 JONES      ACCOUNTING

         7654 MARTIN     ACCOUNTING

         7698 BLAKE      ACCOUNTING

         7782 CLARK      ACCOUNTING

         7788 SCOTT      ACCOUNTING

         7839 KING       ACCOUNTING

         7844 TURNER     ACCOUNTING

         7876 ADAMS      ACCOUNTING

         --中间结果省略

         56 rows selected.    

--使用cross join 实现交叉连接,即笛卡尔集    

    SQL> select empno,ename,dname from emp

      2  cross join dept;

        EMPNO ENAME      DNAME

    ---------- ---------- --------------

         7369 SMITH      ACCOUNTING

         7499 ALLEN      ACCOUNTING

         7521 WARD       ACCOUNTING

         7566 JONES      ACCOUNTING

         7654 MARTIN     ACCOUNTING

         7698 BLAKE      ACCOUNTING

         7782 CLARK      ACCOUNTING

         7788 SCOTT      ACCOUNTING

         7839 KING       ACCOUNTING

         7844 TURNER     ACCOUNTING

         7876 ADAMS      ACCOUNTING

--等值连接(Oracle 写法)

    SQL> select empno,ename,dname from emp,dept where emp.deptno = dept.deptno;

        EMPNO ENAME      DNAME

    ---------- ---------- --------------

         7369 SMITH      RESEARCH

         7499 ALLEN      SALES

         7876 ADAMS      RESEARCH

         --部分结果省略

         7902 FORD       RESEARCH

         7934 MILLER     ACCOUNTING

    14 rows selected.

--等值连接(SQL 99 写法)

    SQL> select e.empno,e.ename,d.dname from emp  e

      2  inner join dept  d

      3  on e.deptno = d.deptno;

        EMPNO ENAME      DNAME

    ---------- ---------- --------------

         7369 SMITH      RESEARCH

         7499 ALLEN      SALES

         7876 ADAMS      RESEARCH

           --部分结果省略

         7902 FORD       RESEARCH

         7934 MILLER     ACCOUNTING

    14 rows selected.

--注意:表别名不支持as 用法

    SQL> select e.empno,e.ename,d.dname from emp as e

      2  inner join dept  d

      3  on e.deptno = d.deptno;

    select e.empno,e.ename,d.dname from emp as e

                                       *

    ERROR at line 1:

    ORA-00933: SQL command not properly ended

--等值连接并增加条件

    SQL> select e.empno,e.ename,d.dname from emp  e,

      2  dept d

      3  where d.deptno = e.deptno

      4    and e.ename = 'SCOTT';

        EMPNO ENAME      DNAME

    ---------- ---------- --------------

         7788 SCOTT      RESEARCH

--非等值连接

--查询雇员的姓名、薪水、级别且部门为的记录

    SQL> select ename,sal,grade

      2  from emp,salgrade

      3  where sal between losal and hisal

      4    and emp.deptno = 20;

    ENAME             SAL      GRADE

    ---------- ---------- ----------

    SCOTT            3000          4

    FORD             3000          4

    JONES            2975          4

    ADAMS            1100          1

    SMITH             800          1 

--使用SQL 99写法实现上述功能

    SQL> select e.ename,e.sal,s.grade

      2  from emp e

      3    join salgrade s

      4      on e.sal between losal and hisal

      5      and e.deptno = 20;

    ENAME             SAL      GRADE

    ---------- ---------- ----------

    SCOTT            3000          4

    FORD             3000          4

    JONES            2975          4

    ADAMS            1100          1

    SMITH             800          1

--右外连接

--注意:右外连接时,加号在等号的左边

--可以看到,左表emp中的列有为空值的

    SQL> select e.ename,e.deptno,d.dname  

      2  from emp e,dept d

      3  where e.deptno(+) = d.deptno;

    ENAME          DEPTNO DNAME

    ---------- ---------- --------------

    CLARK              10 ACCOUNTING

    KING               10 ACCOUNTING

    MILLER             10 ACCOUNTING

    JONES              20 RESEARCH

    FORD               20 RESEARCH

    ADAMS              20 RESEARCH

    SMITH              20 RESEARCH

    SCOTT              20 RESEARCH

    WARD               30 SALES

    TURNER             30 SALES

    ALLEN              30 SALES

    ENAME          DEPTNO DNAME

    ---------- ---------- --------------

    JAMES              30 SALES

    BLAKE              30 SALES

    MARTIN             30 SALES

                       OPERATIONS

    15 rows selected.

--使用SQL 99写法实现右外连接

    SQL> select e.ename,e.deptno,d.dname

      2  from emp e      

      3    right join dept d

      4      on e.deptno = d.deptno ;

    ENAME          DEPTNO DNAME

    ---------- ---------- --------------

    CLARK              10 ACCOUNTING

    KING               10 ACCOUNTING

    MILLER             10 ACCOUNTING

    JONES              20 RESEARCH

    FORD               20 RESEARCH

    ADAMS              20 RESEARCH

    SMITH              20 RESEARCH

    SCOTT              20 RESEARCH

    WARD               30 SALES

    TURNER             30 SALES

    ALLEN              30 SALES

    ENAME          DEPTNO DNAME

    ---------- ---------- --------------

    JAMES              30 SALES

    BLAKE              30 SALES

    MARTIN             30 SALES

                       OPERATIONS

--左外连接

--注意:左外连接时,加号在等号的右边

    SQL> select d.dname,e.ename,e.deptno

      2  from dept d,emp e

      3  where d.deptno = e.deptno(+)

      4  order by d.deptno;

    DNAME          ENAME          DEPTNO

    -------------- ---------- ----------

    ACCOUNTING     CLARK              10

    ACCOUNTING     KING               10

    ACCOUNTING     MILLER             10

    RESEARCH       JONES              20

    RESEARCH       FORD               20

    RESEARCH       ADAMS              20

    RESEARCH       SMITH              20

    RESEARCH       SCOTT              20

    SALES          WARD               30

    SALES          TURNER             30

    SALES          ALLEN              30

    DNAME          ENAME          DEPTNO

    -------------- ---------- ----------

    SALES          JAMES              30

    SALES          BLAKE              30

    SALES          MARTIN             30

    OPERATIONS

    15 rows selected.

--使用SQL 99写法实现左外连接               

    SQL> select d.dname,e.ename,e.deptno

      2  from dept d

      3    left join emp e

      4      on d.deptno = e.deptno

      5  order by d.deptno;

    DNAME          ENAME          DEPTNO

    -------------- ---------- ----------

    ACCOUNTING     CLARK              10

    ACCOUNTING     KING               10

    ACCOUNTING     MILLER             10

    RESEARCH       JONES              20

    RESEARCH       FORD               20

    RESEARCH       ADAMS              20

    RESEARCH       SMITH              20

    RESEARCH       SCOTT              20

    SALES          WARD               30

    SALES          TURNER             30

    SALES          ALLEN              30

    DNAME          ENAME          DEPTNO

    -------------- ---------- ----------

    SALES          JAMES              30

    SALES          BLAKE              30

    SALES          MARTIN             30

    OPERATIONS

    15 rows selected.                

--自连接

    SQL> select e.ename || ' works for ' || m.ename

      2  from emp e,emp m

      3  where e.empno = m.mgr;

    E.ENAME||'WORKSFOR'||M.ENAME

    -------------------------------

    FORD works for SMITH

    BLAKE works for ALLEN

    BLAKE works for WARD

    KING works for JONES

    BLAKE works for MARTIN

    KING works for BLAKE

    KING works for CLARK

    JONES works for SCOTT

    BLAKE works for TURNER

    SCOTT works for ADAMS

    BLAKE works for JAMES

    E.ENAME||'WORKSFOR'||M.ENAME

    -------------------------------

    JONES works for FORD

    CLARK works for MILLER

    13 rows selected.

--自然连接

    SQL> select empno,ename,job,deptno,dname,loc

      2  from emp

      3  natural join dept;

        EMPNO ENAME      JOB           DEPTNO DNAME          LOC

    ---------- ---------- --------- ---------- -------------- -------------

         7369 SMITH      CLERK             20 RESEARCH       DALLAS

         7499 ALLEN      SALESMAN          30 SALES          CHICAGO

         7521 WARD       SALESMAN          30 SALES          CHICAGO

         7566 JONES      MANAGER           20 RESEARCH       DALLAS

         7654 MARTIN     SALESMAN          30 SALES          CHICAGO

         7698 BLAKE      MANAGER           30 SALES          CHICAGO

         7782 CLARK      MANAGER           10 ACCOUNTING     NEW YORK

         7788 SCOTT      ANALYST           20 RESEARCH       DALLAS

         7839 KING       PRESIDENT         10 ACCOUNTING     NEW YORK

         7844 TURNER     SALESMAN          30 SALES          CHICAGO

         7876 ADAMS      CLERK             20 RESEARCH       DALLAS

        EMPNO ENAME      JOB           DEPTNO DNAME          LOC

    ---------- ---------- --------- ---------- -------------- -------------

         7900 JAMES      CLERK             30 SALES          CHICAGO

         7902 FORD       ANALYST           20 RESEARCH       DALLAS

         7934 MILLER     CLERK             10 ACCOUNTING     NEW YORK

    14 rows selected.

--使用using 子句创建连接

    SQL> select e.empno,e.ename,d.dname,d.loc

      2  from emp e

      3  join dept d

      4  using (deptno)

      5  where deptno in (20,40);

        EMPNO ENAME      DNAME          LOC

    ---------- ---------- -------------- -------------

         7369 SMITH      RESEARCH       DALLAS

         7566 JONES      RESEARCH       DALLAS

         7788 SCOTT      RESEARCH       DALLAS

         7876 ADAMS      RESEARCH       DALLAS

         7902 FORD       RESEARCH       DALLAS

点赞
收藏
评论区
推荐文章
kenx kenx
3年前
MySQL查询之内连接,外连接查询场景的区别与不同
前言我在写sql查询的时候,用的最多的就是where条件查询,这种查询也叫内连查询innerjoin,当然还有外连查询outerjoin,左外连接,右外连接查询,常用在多对多关系中,那他们区别和联系是什么呢?内连接innerjoin内连接最常用定义:1.连接结果仅包含符合连接条件的行组合起来作为结果集,参与连接的两个表都应该符合连接条件使用关键词:
Wesley13 Wesley13
3年前
9 MySQL
多表查询:http://www.cnblogs.com/linhaifeng/articles/7267596.html1.多表连接查询2.符合条件连接查询3.子查询一、准备表!(https://oscimg.oschina.net/oscnet/3ed677
Wesley13 Wesley13
3年前
MySQL数据库查询
MySQL数据库查询12、说明:使用外连接A、left(outer)join:左外连接(左连接):包含leftjoin左表所有行,如果左表中某行在右表没有匹配,则结果中对应行右表的部分全部为空(NULL).。SQL:select\fromstudent(表1)leftjoincourse(表2)onstude
Wesley13 Wesley13
3年前
MySQL教程(十四)—— 连接
1 连接的语法结构MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。语法结构:1table_reference//第一张表2{INNER|CROSSJOIN|{LEFT|RIGHT}OUTERJOIN}//连接选择:内连接,左外、右外连接3t
Wesley13 Wesley13
3年前
mysql学习之join从句
一、join从句共有5种类型内连接(innerjoin)全外连接(fullouterjoin)左外连接(leftouterjoin)右外连接(rightouterjoin)交叉连接(crossjoin)二、演示案例新建两张表user1表和user2表CRE
Wesley13 Wesley13
3年前
HQL的多表查询
对象之间总是有各种各样的关系,关联关系是类之间最常见的关系。多表查询是HQL中的强大功能之一,包括内连接、左连接和右连接等。多表查询的设置及运行都比较麻烦,在运行本节中的示例时,务必保证每一步都没有错误。 6.4.1 表之间的关联关系 在数据库joblog中用到了3个表:student(学生表)、course(课程表
Wesley13 Wesley13
3年前
mysql2_4
连接查询将多张表进行记录的连接(按照某个指定的条件进行数据拼接)  最终结果是:记录数有可能变化,字段书一定会增加(至少两张表的合并)  连接查询的意义:在用户查看数据的时候,需要显示的数据来自多张表。  连接查询分类sql中将连接查询分成四类:内连接,外连接,自然连接,交叉连接  1.交叉连接:crossjoi
Wesley13 Wesley13
3年前
MySQL之多表查询
阅读目录一多表联合查询二多表连接查询三复杂条件多表查询四子语句查询五其他方式查询六 SQL逻辑查询语句执行顺序(重点)七外键约束八其他约束类型九表与表之间的关系一.多表联合查询!
Wesley13 Wesley13
3年前
6.多表查询
多表查询本节重点:多表连接查询符合条件连接查询子查询准备工作:准备两张表,部门表(department)、员工表(employee)createtabledepartment(idint,namevarchar(20));
Wesley13 Wesley13
3年前
Hibernate常见知识汇总
1.在数据库中条件查询速度很慢的时候,如何优化?1.建索引2.减少表之间的关联3.优化sql,尽量让sql很快定位数据,不要让sql做全表查询,应该走索引,把数据量大的表排在前面4.简化查询字段,没用的字段不要,已经对返回结果的控制,尽量返回少量数据2.在Hibernate中进行多表查询,每个表中各取几个字段,也就是说查询出来的结果
Easter79
Easter79
Lv1
今生可爱与温柔,每一样都不能少。
文章
2.8k
粉丝
5
获赞
1.2k