《Oracle多表查询》由会员分享，可在线阅读，更多相关《Oracle多表查询（6页珍藏版）》请在金锄头文库上搜索。
1、SQL多表关联! inner join,left join ,right join 我对多表关联的总结:/以下若有理解错误请指出,谢谢!内连接 INNER JOIN:只显示多表之间与关联条件相匹配的列.外连接:LEFT JOIN :以左表为基础,显示左表中的所有列,不管是否与关联条件相匹配,而右表中的数据只显示与关联条件相匹配的列,不匹配的列以NULL字符填充.RIGHT JOIN:以右表为基础,显示右表中的所有列,不管是否与关联条件相匹配,而左表中的数据只显示与关联条件相匹配的列,不匹配的列以NULL字符填充.FULL JOIN :显示多个表中的所有的列,不匹配关联条件的列以NULL字符填充
2、.下面是我写的关于sql 多表关联的sql语句,大家可以在SQL SERVER中操作下试下,是否与上面的对于sql关联的总结相同,查询结果我就不结了,大家试下就知道了!create database mydb;use mydb;create table customers(cust_id int not null identity(1,1) primary key,cust_name varchar(100) not null,cust_tel varchar(50) not null,cust_address varchar(100) not null )create table books
3、(isbn int not null identity(700001,1),book_name varchar(100) not null,price money not null)create table orders(order_id int not null identity(1001,1),isbn int not null,cust_id int not null,order_date datetime not null)insert into customers(cust_name,cust_tel,cust_address) values(书店a,0553-2451466,江苏省
4、);insert into customers(cust_name,cust_tel,cust_address) values(书店b,0553-2215266,江苏省);insert into customers(cust_name,cust_tel,cust_address) values(书店c,0553-8754466,江苏省);insert into customers(cust_name,cust_tel,cust_address) values(书店d,0553-4563466,江苏省);insert into customers(cust_name,cust_tel,cust_
5、address) values(书店e,0553-2145212,江苏省);insert into books(book_name,price) values(php,78);insert into books(book_name,price) values(java,72);insert into books(book_name,price) values(c+,72);insert into books(book_name,price) values(C语言,56);insert into books(book_name,price) values(sql,53);insert into
6、books(book_name,price) values(,69);insert into books(book_name,price) values(asp,43);insert into orders(isbn,cust_id,order_date) values(700001,1,getdate();insert into orders(isbn,cust_id,order_date) values(700002,1,getdate();insert into orders(isbn,cust_id,order_date) values(700003,2,getdate();inser
7、t into orders(isbn,cust_id,order_date) values(700001,1,getdate();insert into orders(isbn,cust_id,order_date) values(700003,1,getdate();insert into orders(isbn,cust_id,order_date) values(700006,1,getdate();insert into orders(isbn,cust_id,order_date) values(700001,2,getdate();insert into orders(isbn,c
8、ust_id,order_date) values(700001,2,getdate();insert into orders(isbn,cust_id,order_date) values(700002,2,getdate();insert into orders(isbn,cust_id,order_date) values(700003,1,getdate();insert into orders(isbn,cust_id,order_date) values(700003,9,getdate();-我下面是故意的,让大家看到效果insert into orders(isbn,cust_
9、id,order_date) values(7000025,9,getdate();select a.order_id,a.isbn,b.book_name,b.price,c.cust_name,c.cust_tel,c.cust_address,a.order_datefrom orders as a inner join books as b ona.isbn=b.isbn inner join customers as cona.cust_id=c.cust_id;select a.order_id,a.isbn,b.book_name,b.price,c.cust_name,c.cu
10、st_tel,c.cust_address,a.order_datefrom orders as a left join books as b ona.isbn=b.isbn left join customers as cona.cust_id=c.cust_id;select a.order_id,a.isbn,b.book_name,b.price,c.cust_name,c.cust_tel,c.cust_address,a.order_datefrom orders as a right join books as b ona.isbn=b.isbn right join custo
11、mers as cona.cust_id=c.cust_id;左表右表指哪些表?select * from table_1 as a left join table_2 as cona.id=b.id leftjoin table_3 as conb.isbn=c.isbn 疑问中.第一种认为:认为最左边的表左表 table_1右表就是除了左表的其它表,而不是最右边的表所以这里的左表是 table_1,那右表是table_2,table_3第二种认为:table_1 是table_2的左表,table_2是table_3的左表CSDN SQL专区提问,竟请您的加盟:还请牛人指点!最终答案:ta
12、ble_1 左连接 table_2 以table_1为基础进行连接运算，得到新表即临时表（#table）。#table 左连接 table_3 以table# 为基础进行连接运算，得到最终结果。与我的总结一致!以下是来自网上的关于关联说的比较好的文章(易懂)来自:表A记录如下：aID aNum1 a200501112 a200501123 a200501134 a200501145 a20050115表B记录如下:bID bName1 20060324012 20060324023 20060324034 20060324048 2006032408实验如下:1. left joinsql语句如下: SELECT * FROM ALEFT JOIN B ON A.aID = B.bID结果如下:aID aNum bID bName1 a20050111 1 20060324012 a20050112 2 20060324023 a20050113 3 20060324034 a20050114 4 20060324045 a20050115 NULL NULL（所影响的行数为 5 行）结果说明: left join是以A表的记录为基础的,A可
