1 LEFT JOIN: The query results are based on the left table data. If the left table has four data, the right table has three data, the query result is four, and all the data in the left table.For example:EMP Table:Sal table:Left connection Select * from EMP left join SAL on
EMP. ename = SAL.ename; Left JOIN, the table EMP is the primary table, so the q
Tags: style blog c http a widthFor SQL joins, learning may be a bit confusing. We know that the join syntax for SQL has a lot of inner, outer, left, and sometimes it's not very clear what the result set looks like for a select. There is an article on Coding horror that explains the join of SQL through the Venn diagrams of the Venturi diagram. I feel clear and understandable, turn around.Let's say we have tw
Left join returns records that include all records in the left table and join fields in the right table.Right join returns records that include all records in the right table and the joined fields in the left table.Inner join (equivalent join) returns only rows with equal
, c.cat_name from Mingoods G, category C WHERE G. cat_id = c.cat_id; 2. Left join query ... on ... syntax : select a.filed, [A.filed2, .....,] b.filed, [b.filed4 ...,] from join L T;right table> as B on Suppose there is a, b two tables, the left connection query is a table on the left, B table on the right, A and B table through a relationship to correlate rows, B to match a table. 2.1 Take a loo
Test table:EMP Table Sal TableLeft JOIN: Results All rows are displayed in the table, and the right table determines that the column is the same as the left.SELECT * FROM EMP left join SAL on EMP. ename = SAL. ename; Right join: Results All rows are displayed in the table on the left, the table on the right is consistent with the tableSelect * from EMP right
Reprinted from: http://www.cnblogs.com/still-windows7/archive/2012/10/22/2734613.htmlPrerequisites: Suppose there are two tables, one is the student table and the other is the Student score table.The table data are:One, internal connection-inner jion:The most common connection query may be that of identifying the student's name and score:Select S.name,m.mark from student S,mark m where S.id=m.studentidThe above is our most common inner join, that is,
Let's start by looking at the results of the left join and the right join and the Inner join and the full join working on the table.
Create a new two table in the database and insert the data you want to test.
New table:[SQL]View PlainCopyUse [Test]GO/****** object: Table [dbo].[ EMP] Script Date: 06/22/2
Tags: http strong ar Data div sp on ad efLet's start by looking at the results of the left join and the right join and the Inner join and the full join working on the table.
Create a new two table in the database and insert the data you want to test.
New table:[SQL]Use [Test]
GO
/****** object: Table
Label:Transferred from: http://blog.csdn.net/jz20110918/article/details/41806611 Let's say we have two tables. Table A is the sheet on the left. Table B is the list on the right. Each of them has four records, of which two records name is the same, as follows: Let's look at the different joins
Table A
Id
Name
1
Pirate
2
Monkey
3
Ninja
4
Spaghetti
Label:Original: http://blog.csdn.net/shadowyelling/article/details/7684714Left Join: Returns all the information in the table on the right and the information related to the left table conditionRight Join: Returns all of the information in the table in the list and information about the right table condition in the left tableInner Join: Returns information common
Differences between inner join, left JOIN, right join, outer join in SQLFor example, you'll know!Table A (A1,B1,C1) b (A2,B2)A1 B1 C1 A2 B201 Mathematics 95 01 Sheets Three02 Language 90 02 John Doe English Harry Select A.*, b.* from AINNER JOIN B on (A.A1=B.A2)The result is
Take a look at the results of the left join and the right join with the Inner join and the full join to manipulate the table.
Create a new two table in the database and insert the data you want to test.
New table:[SQL]View PlainCopy
Use [Test]
GO
/****** object: Table [dbo].[ EMP] Script Date: 06/
The join syntax is as follows: select [field] from [Table Name 1] inner/left/right/full join [Table name 2] on [Table Name 1. field 1] [Table name 2. field 2] cross join: the product of the flute. without any constraints, the number of rows in a table is multiplied by the number of rows in another table. Left join: re
This connection is often used in early query statements, which is a small problem. Therefore, I checked the information and summarized it as follows:
External connections are easier to understand,A. It is generally used in some classification codes. For example, if you have an employee information table, the position information isCodeAnd the meaning of this Code is described in the position table.B. Classified information storage. For example, in the preceding employee information table, the
A. =
Eg.
Select a. a B. B from a, B where a. A = B. A and A. C = 'herengang ';
Under this condition, it only shows the data that a. A = B. A and A. C = "herengang ".
Although it there is data which. C is "herengang", but if we can't find such record which. A value equals to. A in Table B, then it will be cleared.
B. Left join
Select a. a, B.
From Table
Left join Table B
On a. A = B. A and A. C = "herengang"
[Test @ ora1] SQL> select * From A; No. Name ---- ---------- 1000 Zhang San 2000 Li Si 3000 Wang Wu [test @ ora1] SQL> select * from B; product NO. ---- ---------- 1000 TV set 2000 video recorder 4000 bicycle [test @ ora1] SQL> set null Null Value -- Here I define null as [null value] [test @ ora1] SQL> select. *, B. * From a inner join B on. no. = B. no.; No. name No. Product ---- ---------- 1000 Zhang San 1000 TV set 2000 Li Si 2000 video recorder [
Tags: assigning local data comparison operator mem Quick object Pop resultINNER JOIN (equivalent join) returns only rows that have the same join field in two tablesLeft join returns records that include all the records in the left table and the equivalent of the junction fields in the right tableRight
First, take a look at some of the simplest examples.
Example
Table AAid Adate1 A12 A23 A3
TableB
Bid Bdate1 B12 B24 B4Two table a,b connected, to remove fields with the same IDSELECT * from a INNER join B on a.aid = B.bid This is the only matching data to be fetched.At this point, the removal is:1 A1 B12 A2 B2
So the left join means:SELECT * from a LEFT join
Label:INNER JOIN (equivalent join) returns only rows that have the same join field in two tablesLeft join returns records that include all the records in the left table and the equivalent of the junction fields in the right table Right join returns records that include all r
INNER JOIN ( equivalent connection ) returns only rows in which the junction fields are equal in two tablesLeft join ( left Join ) returns records that include all records in the left table and the equivalent of the junction fields in the right tableRight join ( right Join )
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.