📄 sql_join.asp
字号:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en-US" xml:lang="en-US" xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SQL Join</title>
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<meta name="Keywords" content="xml,tutorial,html,dhtml,css,xsl,xhtml,javascript,asp,ado,vbscript,dom,sql,colors,soap,php,authoring,programming,training,learning,beginner's guide,primer,lessons,school,howto,reference,examples,samples,source code,tags,demos,tips,links,FAQ,tag list,forms,frames,color table,w3c,cascading style sheets,active server pages,dynamic html,internet,database,development,Web building,Webmaster,html guide" />
<meta name="Description" content="Well organized easy to understand SQL tutorial with lots of examples. Including PHP, ASP, ADO, Oracle, Access, SQL Server. Related: HTML, JavaScript, XML, XQuery" />
<meta http-equiv="pragma" content="no-cache" />
<meta http-equiv="cache-control" content="no-cache" />
<link rel="stylesheet" type="text/css" href="../stdtheme.css" />
<script type="text/javascript">
var gaJsHost = (("https:" == document.location.protocol) ? "../../https@ssl./default.htm" : "../../www./default.htm");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
</script>
<script type="text/javascript">
var pageTracker = _gat._getTracker("UA-3855518-1");
pageTracker._initData();
pageTracker._trackPageview();
</script>
</head>
<body>
<a name="top"></a>
<table cellpadding="0" cellspacing="0" width="100%" bgcolor="#808080">
<tr>
<td width="234" valign="top">
<a href="../default.htm"><img src="../images/w3default80.jpg" border="0" alt="W3Schools" /></a>
</td>
<th valign="middle" align="left" class="right">
<script type="text/javascript"><!--
google_ad_client = "pub-3440800076797949";
/*txtimg*/
google_ad_slot = "5330033957";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript" src="../../pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
<noscript>
<a href="../default.asp" target="_blank"><img src="../banners/w6.gif" border="0" alt="W3Schools" /></a>
</noscript>
<br /></th>
</tr>
</table>
<table style="margin-left:1px" width="800px" border="0" cellpadding="0" cellspacing="2">
<tr>
<td width="137" class="content" valign="top"><br />
<a class="left" href="../default.asp" target="_top">
<img src="../images/homeicon.gif" border="0" alt="home" /></a>
<a class="left" href="../default.asp" target="_top">
<b>HOME</b></a>
<br /><br />
<b>SQL Basic</b>
<br />
<a class="left" target="_top" href="default.asp" >SQL HOME</a><br />
<a class="left" target="_top" href="sql_intro.asp" >SQL Intro</a><br />
<a class="left" target="_top" href="sql_select.asp" >SQL Select</a><br />
<a class="left" target="_top" href="sql_where.asp" >SQL Where</a><br />
<a class="left" target="_top" href="sql_insert.asp" >SQL Insert</a><br />
<a class="left" target="_top" href="sql_update.asp" >SQL Update</a><br />
<a class="left" target="_top" href="sql_delete.asp" >SQL Delete</a><br />
<br />
<b>SQL Demo</b><br />
<a class="left" target="_top" href="sql_tryit.asp" >SQL Try It</a><br />
<br />
<b>SQL Advanced</b><br />
<a class="left" target="_top" href="sql_orderby.asp" >SQL Order By</a><br />
<a class="left" target="_top" href="sql_and_or.asp" >SQL and & or</a><br />
<a class="left" target="_top" href="sql_in.asp" >SQL In</a><br />
<a class="left" target="_top" href="sql_between.asp" >SQL Between</a><br />
<a class="left" target="_top" href="sql_alias.asp" >SQL Aliases</a><br />
<a class="left" target="_top" href="sql_join.asp" style='font-weight:bold;'>SQL Join</a><br />
<a class="left" target="_top" href="sql_union.asp" >SQL Union</a><br />
<a class="left" target="_top" href="sql_create.asp" >SQL Create</a><br />
<a class="left" target="_top" href="sql_drop.asp" >SQL Drop</a><br />
<a class="left" target="_top" href="sql_alter.asp" >SQL Alter</a><br />
<a class="left" target="_top" href="sql_functions.asp" >SQL Functions</a><br />
<a class="left" target="_top" href="sql_groupby.asp" >SQL Group By</a><br />
<a class="left" target="_top" href="sql_select_into.asp" >SQL Select Into</a><br />
<a class="left" target="_top" href="sql_view.asp" >SQL Create View</a><br />
<a class="left" target="_top" href="sql_server.asp" >SQL Server</a><br />
<br />
<a class="left" target="_top" href="sql_quickref.asp" >SQL Quick Ref</a><br />
<a class="left" target="_top" href="sql_summary.asp" >SQL Summary</a><br />
<br />
<b>SQL Quiz</b><br />
<a class="left" target="_top" href="sql_quiz.asp" >SQL Quiz</a><br />
<br />
<b>Selected Reading</b><br />
<a class="left" target="_top" href="../browsers/browsers_stats.asp">Web Statistics</a><br />
<a class="left" target="_top" href="../site/site_glossary.asp">Web Glossary</a><br />
<a class="left" target="_top" href="../hosting/default.asp">Web Hosting</a><br />
<a class="left" target="_top" href="../quality/default.asp">Web Quality</a><br /><br />
<a class="left" target="_top" href="../forum/default.asp">W3Schools Forum</a><br /><br />
<a class="left" target="_top" href="../about/about_helping.asp">Helping W3Schools</a><br /><br />
<script type="text/javascript"><!--
google_ad_client = "pub-3440800076797949";
/*LeftLinkUnit*/
google_ad_slot = "4854527104";
google_ad_width = 120;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="../../pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</td>
<td valign="top">
<table style="border: 1px solid gray" width="100%" bgcolor="#FFFFFF" border="0" cellpadding="5" cellspacing="0">
<tr>
<td>
<h1>SQL JOIN</h1>
<a href="sql_alias.asp"><img border="0" alt="previous" src="../images/btn_previous.gif" /></a>
<a href="sql_union.asp"><img border="0" alt="next" src="../images/btn_next.gif" /></a>
<hr />
<h2>Joins and Keys
</h2>
<p>Sometimes we have to select data from two or more tables to make our result complete.
We have to perform a join.
</p>
<p>Tables in a database can be related to each other with keys. A primary key is a column
with a unique value for each row. Each primary key value must be unique within
the table. The purpose is to bind data together, across tables, without
repeating all of the data in every
table.
</p>
<p>In the "Employees" table below, the "Employee_ID" column is the
primary key, meaning that <b>no</b> two rows can have the same Employee_ID. The
Employee_ID
distinguishes two
persons even if they have the same name.
</p>
<p>When you look at the example tables below, notice that:
</p>
<ul>
<li>The "Employee_ID" column is the primary key of the "Employees"
table</li>
<li>The "Prod_ID" column is the primary key of the "Orders"
table</li>
<li>The "Employee_ID" column in the "Orders" table is used to refer to the
persons in the "Employees" table without using their names</li>
</ul>
<hr />
<p><b>Employees</b>:</p>
<table class="ex" cellspacing="0" border="1" width="60%">
<tr>
<th width="25%" align="left">Employee_ID</th>
<th width="35%" align="left">Name</th>
</tr>
<tr>
<td>01</td>
<td>Hansen, Ola</td>
</tr>
<tr>
<td>02</td>
<td>Svendson, Tove</td>
</tr>
<tr>
<td>03</td>
<td>Svendson, Stephen</td>
</tr>
<tr>
<td>04</td>
<td>Pettersen, Kari</td>
</tr>
</table>
<p><b>Orders:</b></p>
<table class="ex" cellspacing="0" border="1" width="60%">
<tr>
<th width="15%" align="left">Prod_ID</th>
<th width="20%" align="left">Product</th>
<th width="25%" align="left">Employee_ID</th>
</tr>
<tr>
<td>234</td>
<td>Printer</td>
<td>01</td>
</tr>
<tr>
<td>657</td>
<td>Table</td>
<td>03</td>
</tr>
<tr>
<td>865</td>
<td>Chair</td>
<td>03</td>
</tr>
</table>
<br />
<hr />
<h2>Referring to Two Tables</h2>
<p>We can select data from two tables by referring to two tables, like this:</p>
<h3>Example</h3>
<p>Who has ordered a product, and what did they order?</p>
<table class="ex" cellspacing="0" border="1" width="100%">
<tr>
<td><pre>SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID</pre></td>
</tr>
</table>
<p><b>Result</b></p>
<table class="ex" cellspacing="0" border="1" width="60%">
<tr>
<th width="35%" align="left"><b>Name</b></th>
<th width="25%" align="left">Product</th>
</tr>
<tr>
<td>Hansen, Ola</td>
<td>Printer</td>
</tr>
<tr>
<td>Svendson, Stephen</td>
<td>Table</td>
</tr>
<tr>
<td>Svendson, Stephen</td>
<td>Chair</td>
</tr>
</table>
<h3>Example</h3>
<p>Who ordered a printer?</p>
<table class="ex" cellspacing="0" border="1" width="100%">
<tr>
<td><pre>SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
AND Orders.Product='Printer'</pre></td>
</tr>
</table>
<p><b>Result</b></p>
<table class="ex" cellspacing="0" border="1" width="60%">
<tr>
<th align="left"><b>Name</b></th>
</tr>
<tr>
<td>Hansen, Ola</td>
</tr>
</table>
<br />
<hr />
<h2>Using Joins</h2>
<p>OR we can select data from two tables with the JOIN keyword, like this:</p>
<h3>Example INNER JOIN</h3>
<p><b>Syntax</b></p>
<table class="ex" cellspacing="0" border="1" width="100%">
<tr>
<td><pre>SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield</pre></td>
</tr>
</table>
<p>Who has ordered a product, and what did they order?</p>
<table class="ex" cellspacing="0" border="1" width="100%">
<tr>
<td><pre>SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID</pre></td>
</tr>
</table>
<p>The INNER JOIN returns all rows from both tables where there is a match. If there are rows in
Employees that do not have matches in Orders, those rows will <b>not</b> be
listed.</p>
<p><b>Result</b></p>
<table class="ex" cellspacing="0" border="1" width="60%">
<tr>
<th width="35%" align="left"><b>Name</b></th>
<th width="25%" align="left">Product</th>
</tr>
<tr>
<td>Hansen, Ola</td>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -