table has a value, and no match is substituted with a null value.SELECT * from A left OUTER JOIN B on a.name = B.nameA 3.RIGHT [OUTER] JOIN produces a full set of Table B, and a match in table A has a value, and no match is substituted with a null value.SELECT * from A righ
. The 4.FULL [OUTER] JOIN produces a and B's set. For records that do not have a match, NULL is the value.SELECT * from TableA full OUTER JOIN TableB on tablea.name = Tableb.nameYou can find the value without matching by the IS n
An introduction to the differences between inner join, outer join, and Cross join in SQL:There are two tables, table A is the one on the left. Table B is the list on the right. Each of them has four records, of which two records have the same name:The result of the 1.INNER JOIN
statement 8 queries are clearly different, and the results shown in statement 8 are difficult to understand. Therefore, it is recommended that when writing a connection query, on is followed only by the join condition, and the conditions for the intermediate table restrictions are written in the WHERE clause. Statement 9: Full outer
Deep understanding of SQL four connections-left outer connection, right outer connection, inner connection, full connection1. Internal connection(typical join operation, using a comparison operator like = or An inner join uses a c
Four types of connections to SQL-left outer, right outer, inner, fully connected
Today, when I look at a data table for a legacy system, I find that the view we are looking for is full out join. Cause the usual data records to do some restrictive processing, you can also se
is[Email protected]> select * from a LEFT outer join B on A.A=B.A;A B C a D E---------- ---------- ---------- ---------- ---------- ----------1 1 1 1 4 42 2 2 2 5 53 3 34. Right outer connection[Email protected]> select * from a b where A.A (+) =B.A;Another way to do this is[Email protected]> Select * from a right outer
From: http://www.williamlong.info/info/archives/50.html
In a formal database environment, we often encounter this situation: the required data is not stored in the same data table. At this time, you need to use join.
Of course, how to combine the data of different databases depends on how you use it. There are four different Join methods, in this article, we will introduce you to inner
right table, regardless of whether the table on the left has matching data: Select S.name,m.mark from student s right join Mark M on S.id=m.studentid Iv. fully connected-full join: Using the format as above has been explained above Remove the data from the left and right two tables, whether or not they match: Select S.name,m.mark from student s
table in the associated result set row are null (NULL).(2) SQL statement:SELECT *From table1 left join table2 on Table1.id=table2.id-------------Results-------------ID Name ID Score------------------------------1 Lee 1 902 Zhang 2 1004 Wang Null NULL------------------------------NOTE: All clauses that contain table1, return table2 corresponding fields according to specified criteria, non-conforming null di
is called the OUTER join Or take the above data sheet for example, list all the orders, and the person who ordered them-if any SELECT Persons.lastname, Persons.firstname, Orders.orderno from
Persons right
JOIN Orders on
persons.id_p =orders.id_p
ORDER by Persons.lastname
Result set:
LastName
FirstName
OrderNo
Adams
of course join how to combine the data of different database, also depends on how you use it, there are four different ways of join, in this article we will introduce you Inner join and Outer join and its application.
In a formalized database environment, we often encounter
Full (outer) join is the syntax used to fully connect two tables. That is to say, if you want to associate Table A with table B, you can obtain records that exist in Table A but not in Table B, or that exist in Table B but not in table.
The on clause is used to determine whether the record exists.
The following is an example:
Label:--Build Table Table1,table2:CREATE TABLE table1 (ID int,name varchar (10))CREATE TABLE table2 (ID int,score int)Insert INTO table1 Select 1,leeInsert INTO table1 Select 2,zhangInsert INTO table1 Select 4,wangInsert INTO table2 Select 1,90Insert INTO table2 Select 2,100Insert INTO table2 select 3,70such as table-------------------------------------------------Table1 | table2 |-------------------------------------------------ID Name |id Score |1 Lee | 90 |2 Zhang 100 |4 Wang |3 70 |---------
two results were met, and the results were as follows: Second, left join connect-left join: Left join is to select all the tuples from the left table: Select S.name,m.mark from student s left join Mark M on S.id=m.studentid The above statement is the left side of the table, that is, the tuple in the student table is
-right join: The right connection is to remove all data from the right table, regardless of whether the table on the left has matching data: Select S.name,m.mark from student s right join Mark M on S.id=m.studentid The above statement takes all the data from the Mark score table, regardless of whether there is a data match in the student table, as shown in: Iv. fully connected-
: The right connection is to remove all data from the right table, regardless of whether the table on the left has matching data: Select S.name,m.mark from student s right join Mark M on S.id=m.studentid The above statement takes all the data from the Mark score table, regardless of whether there is a data match in the student table, as shown in: Iv. fully connected-full
Let's look at one of the simplest inner joins, inner the corresponding class information from the Join Class table when reading the student table:Static voidMain (string[] args) { using(varwriter =NewStreamWriter (Watchsqlpath,false, Encoding.UTF8)) { using(Dbappdatacontext db =NewDbappdatacontext ()) {db. Log=writer; //INNER JOIN varquery = fromSinchdb. Students
SQL table join query (inner join, full join, left join, right join)
Prerequisites: Assume that there are two tables, one is the student table and the other is the student renewal table
, teacher) (4, principal)Internal connection results:Select a.*,b.* from A inner join B on a.id=b.id;1 31 students2 Li 42 TeacherLeft JOIN Connection results:Select a.*,b.* from A left join B on A.id=b.id;1 31 students2 Li 42 Teacher3 Harry NULL NULLRight Join Result:Select a.*,b.* from A right
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.