SQL内连接和左连接的区别 - 使用SQLite演示

SQL INNER JOIN 关键字
在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
INNER JOIN 与 JOIN 是相同的。

SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。

 

也就是；

    INNER JOIN只返回左表和右表都有数据的行；LEFT JOIN 返回左表所有的行；

    INNER JOIN  =  JOIN

    LEFT JOIN  =  LEFT OUTER JOIN

Sqlite中示例如下；

全部的CMD操作如下；

Microsoft Windows [版本 6.1.7601]
Copyright (c) 2010 Microsoft Corporation.  All rights reserved.

S:\sqlite>sqlite3 wctest1.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table Persons(
   ...> Id_P int primary key not null,
   ...> LastName varchar(255),
   ...> FirstName varchar(255),
   ...> Address varchar(255),
   ...> City varchar(255));
sqlite> INSERT INTO Persons VALUES (1, 'Adams', 'John', 'Oxford Street', 'London
');
sqlite> INSERT INTO Persons VALUES (2, 'Bush', 'George', 'Fifth Avenue', 'New Yo
rk');
sqlite> INSERT INTO Persons VALUES (3, 'Carter', 'Thomas','Changan Street', 'Bei
jing');
sqlite> create table Orders(
   ...>  Id_O int primary key not null,
   ...>  OrderNo varchar(50),
   ...>  Id_P int);
sqlite> INSERT INTO Orders VALUES (1, '77895', 3);
sqlite> INSERT INTO Orders VALUES (2, '44678', 3);
sqlite> INSERT INTO Orders VALUES (3, '22456', 1);
sqlite> INSERT INTO Orders VALUES (4, '24562', 1);
sqlite> INSERT INTO Orders VALUES (5, '34764', 65);
sqlite> select * from Persons;
1|Adams|John|Oxford Street|London
2|Bush|George|Fifth Avenue|New York
3|Carter|Thomas|Changan Street|Beijing
sqlite> select * from Orders;
1|77895|3
2|44678|3
3|22456|1
4|24562|1
5|34764|65
sqlite> SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
   ...> FROM Persons
   ...> INNER JOIN Orders
   ...> ON Persons.Id_P=Orders.Id_P
   ...> ORDER BY Persons.LastName;
Adams|John|22456
Adams|John|24562
Carter|Thomas|77895
Carter|Thomas|44678
sqlite> SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
   ...> FROM Persons
   ...> left JOIN Orders
   ...> ON Persons.Id_P=Orders.Id_P
   ...> ORDER BY Persons.LastName;
Adams|John|22456
Adams|John|24562
Bush|George|
Carter|Thomas|44678
Carter|Thomas|77895
sqlite>

为了看的清楚些；看一下参照的原来的数据；

内连接；

左连接；

SQL：

create table Persons( Id_P int primary key not null, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255));INSERT INTO Persons VALUES (1, 'Adams', 'John', 'Oxford Street', 'London'); INSERT INTO Persons VALUES (2, 'Bush', 'George', 'Fifth Avenue', 'New York'); INSERT INTO Persons VALUES (3, 'Carter', 'Thomas','Changan Street', 'Beijing');create table Orders( Id_O int primary key not null, OrderNo varchar(50), Id_P int);INSERT INTO Orders VALUES (1, '77895', 3); INSERT INTO Orders VALUES (2, '44678', 3); INSERT INTO Orders VALUES (3, '22456', 1); INSERT INTO Orders VALUES (4, '24562', 1); INSERT INTO Orders VALUES (5, '34764', 65);SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName;SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons left JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName;

 

总结

以上是生活随笔为你收集整理的SQL内连接和左连接的区别 - 使用SQLite演示的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。