How SQL joins two tables with multiple conditions

The implementation method and detailed operation steps are as follows:

1. In the first step, build two tables, then insert the data separately, as shown in the following figure, and then enter the next step.

2. Secondly, left

join: left join. Even if there is no match in the right table, all rows are returned from the left table. If the right table doesn't match, fill it as empty as shown in the image below and move on to the next step.

<>5. Finally, cross join: cross join, the multiple of the two tables select * from emp cross join nation, as shown in the following figure. In this way, the problem is solved.

1. First, create two tables and insert data separately.

2. Left join: Left join, even if there is no match in the right table, all rows will be returned from the left table, and the unmatched right table will be filled with null.

3. Right join: right join, even if there is no match in the left table, all rows will be returned from the right table.

4. Full join: complete join, as long as there is a match in one of the tables, the row will be returned.

5. Cross join: Cross join, the multiple of the two tables select * from emp cross join nation.

For example: select ,,from test1 t1 ,test2 t2 where

That's it.

select * from test1 a

left join test2 b on a.Date=bDate and aNumber = bNumber.

select * from test1,test2 where and 或。

select (which table you want to display the field from) from test1,test2 where and

With where the connection can be clearly written condition, I also ran into this problem, and now it has been solved.

Just write two conditions in the where statement, separated by and.

The easiest way to do this is here.

Method 1: select , from a

inner join b on

where method two:

select , from a,b

where and

1. Open the database management tool and create two new tables in the database for testing, here, the table structure of the two tables should be the same, and establish test and test1 respectively.

2. Open a new SQL query window and create a stored procedure. This means that the data inserted into test is inserted into test1.

3. Click 'Execute', and a trigger named 't' will be created.

4. Table structure, we can also see the new trigger 't'.

5. Now insert a piece of data into the test table for testing. insert into values('1','simon','25')。

6. The data insertion is successfully performed twice, so let's see if the data in test1 has been updated in time. select * from After testing, the data is successfully written.

where ..and join....on.

1. Where is the where condition is attached after the two tables are joined.

2. And and is to filter which records in table A or table B meet the connection conditions before table joining, and at the same time, it will take into account whether it is left join or right join. Namely.

In the case of a left-hand join, if a record in the left table does not meet the join conditions, it will not join, but will remain in the result set (in this case, the right-hand join result is null). The ON condition is a condition used when generating a temporary table, and it returns the records in the left table regardless of whether the ON condition is true or not.

3. It is recommended to use where to filter conditions as much as possible.

Comparison: 1. SQL and result set after using AND. There is still a 383 result set.

2. Use the SQL and result set after where. There is no such record.

(inner) join on The inner link is the same as the where subquery.

The main difference is left join, right join, and full join, and the functionality and performance are different from where.

inner join

Equal-value joins) returns only rows in both tables where the join fields are equal.

The connection between where and inner join is not fundamentally different, but the result is the same.

1) In terms of efficiency, where may have the same efficiency as inner join. But what is almost certain (via sqlserver help and other sources, as well as this test) is that join is no less efficient than where.

2) Use join to check for invalid or miswritten association conditions in a statement.

3) From a readability perspective, where is more intuitive.

left join

left outer join

Left join) returns records that include all records in the left table and the join fields in the right table.

The result set of a left-out join includes all the rows of the left table specified in the left outer clause, not just the rows that match the join column. If a row in the left table has no matching rows in the right table, all picklist columns in the right table are null in the associated result set rows.

right join

right outer join

Right join) returns records that include all records in the right table and the join fields in the left table.

A right-out join is the reverse join of a left-out join. All rows of the right table are returned. If a row in the right table does not have a matching row in the left table, a null value is returned for the left table.

full join

full outer join

Full join) full outer join returns all rows in the left and right tables.

When a row doesn't have a matching row in another table, the picklist column of the other table contains a null value.

If there are matching rows between the tables, the entire result set row contains the data values for the base table.

join & where to summarize:

Where is displayed only when the data in the two tables meet the common conditions.

jion on is the same condition match.

Therefore, except for the inner join, the results are usually not the same.

1. The first thing is to create a few tables that have no relationship, but note that you must use the same data type as the primary key table on the table that will be used as the foreign key table.

2. Set the row that can be uniquely identified as the primary key, similar to other tables.

3. Next, add the relationship, as shown in the following figure.

4. Drag the relationship that needs to be added and look at the picture directly.

Two different databases on one server.

For example, table A of the test1 database and table b of the test2 database.

It can be written like this.

Select a database, such as test1

select search field.

from a

inner jion b on b.Search field = aSearch field.

where.

2 databases on 2 servers.

If it's cross-server, you'll need to create a database link.

Start by establishing a connection to the server.

use master

goif exists (select * from sysservers where srvname = 'linkserver')

beginexec sp_dropserver 'linkserver', 'droplogins'

endgoexec sp_addlinkedserver 'linkserver','','sqloledb',''

exec sp_addlinkedsrvlogin 'linkserver','false',null,'sa','command'

golinkserver is the name of the connection server, and you can take whatever you like.

sa，command。It is the IP and login user password of the other database you want to connect to.

Query command: select to retrieve a field.

from a

inner jion on b.Search field = aSearch field.

where.

on the same server. You can associate it by writing the full name directly.

Different servers. You can establish a connection to the database and then associate it.

The same server is easy to say directly associated with the query, different servers, you need to use opendatasource, as for the 2005 database connection is the same as 2000, if it is the default instance installation, if the new instance, you need to add the instance name after the connection.

Since there is no connection, it is good to query separately, and how to display the query together.

select field from (select * from shopa where place=1) as shopa,(select * from shopb where place=1) as shopb

Basically:

select * from msginfo a,userinfo b,userinfo c where and

You can replace it with the corresponding prefix + field you want to query.

That is, the userinfo table is used twice to associate the two fields of the msginfo table.

If you have a few tables, you have to join a few of them.

Assumptions: select, from table1 a, table2 b, table3 cwhere

This will join the three tables.

Mainly, there must be fields in these tables that can be joined.

If you want two tables to have a field that is related, for example, n tables have id fields, and you can join them according to this one field.

Use join on to match the connection conditions of each table.