SQL等链接剖析
SQL等链接是广大程序员们经常涉及到的知识点,但是很多的程序员都是一知半解,今天sojson给大家详细的剖析SQL等链接,希望能给你的工作带来帮助!
SQL 中的等值连接是什么?
SQL EQUI JOIN 针对关联表的相等或匹配列值执行 JOIN。等号 (=) 在 where 子句中用作比较运算符来引用相等性。
您还可以通过使用 JOIN 关键字后跟 ON 关键字来执行 EQUI JOIN,然后指定列名称及其关联表以检查相等性。
句法:
SELECT column_list
FROM table1, table2....
WHERE table1.column_name =
table2.column_name;
or:
SELECT *
FROM table1
JOIN table2
[ON (join_condition)]
例子:
下面是 SQL 中 Equi Join 的示例。
示例表:代理
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | |
| A003 | Alex | London | 0.13 | 075-12458969 | |
| A008 | Alford | New York | 0.12 | 044-25874365 | |
| A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | |
| A010 | Santakumar | Chennai | 0.14 | 007-22388644 | |
| A012 | Lucida | San Jose | 0.12 | 044-52981425 | |
| A005 | Anderson | Brisban | 0.13 | 045-21447739 | |
| A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | |
| A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | |
| A006 | McDen | London | 0.15 | 078-22255588 | |
| A004 | Ivan | Torento | 0.15 | 008-22544166 | |
| A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | |
+------------+----------------------+--------------------+------------+-----------------+---------+
示例表:客户
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
|CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 |
| C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 |
| C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 |
| C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 |
| C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 |
| C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 |
| C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 |
| C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 |
| C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 |
| C00019 | Yearannaidu | Chennai | Chenna
使用以下条件连接所述两个表后,从代理表中获取代理名称列,并从客户表中获取客户名称和客户城市列 -
1.坐席工作区域和客户表的客户城市必须相同,
可以使用以下SQL语句:
SQL代码:
SELECT agents.agent_name,customer.cust_name,
customer.cust_city
FROM agents,customer
WHERE agents.working_area=customer.cust_city;
输出:
AGENT_NAME CUST_NAME CUST_CITY
---------------------------------------- ---------------------------------------- ------------
Ravi Kumar Ravindran Bangalore
Ramasundar Ravindran Bangalore
Subbarao Ravindran Bangalore
Ravi Kumar Srinivas Bangalore
Ramasundar Srinivas Bangalore
Subbarao Srinivas Bangalore
Ravi Kumar Rangarappa Bangalore
Ramasundar Rangarappa Bangalore
Subbarao Rangarappa Bangalore
Ravi Kumar Venkatpati Bangalore
Ramasundar Venkatpati Bangalore
Subbarao Venkatpati Bangalore
Anderson Fleming Brisban
Anderson Jacks Brisban
Anderson Winston Brisban
Santakumar Yearannaidu Chennai
...........
...........
SQL中的等连接和内连接有什么区别?
等值连接是具有包含相等运算符的连接条件的连接。等值连接仅返回指定列具有相等值的行。
内部联接是两个或多个表的联接,它仅返回满足联接条件的行(使用比较运算符进行比较)。
图解:SQL 等值连接与等值连接SQL 内连接
版权所属:SO JSON在线解析
原文地址:https://www.sojson.com/blog/501.html
转载时必须以链接形式注明原始出处及本声明。
如果本文对你有帮助,那么请你赞助我,让我更有激情的写下去,帮助更多的人。