【left join】 主表的条件判断放在where里不要放and，主表的and筛选

1)跟主表有关的条件判断放在and里，会在join的时候带上这个条件。然后符合条件的筛选出来 不符合的最后还是要insert进去的。（再拼回去的）所以放where里

 

2)

 a)left outer join 后的and  是join前附带and条件然后在join拼接  （一个是要在join之前 拼的附表就必须提前满足条件）

 b)where是join后再筛选 （一个是在拼完后再筛选附表 符合条件的）

 

3)

主表 条件 放where里

 

4）

filter的功能都是放在主表  或者 附表的 left outer里， 因为比如主表是学生，学生有地址字段，老师表也有地址字段。

主表学生left out join老师表。有可能学生没有老师。 但是filter地址的时候不可能 还要加上附表的老师的地址字段。我只要filter主表学生的

-------------Create Test Table------------------------
USE [Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [dbo].[Teacher1](
    [TNo] [int] NOT NULL,
    [TName] [nchar](10) NULL,
    [TStatus] [nchar](10) NULL,
 CONSTRAINT [PK_Teacher1] PRIMARY KEY CLUSTERED 
(
    [TNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Student1](
    [SNo] [int] NOT NULL,
    [SName] [nchar](10) NULL,
    [SStatus] [nchar](10) NULL,
    [TNo] [int] NULL,
 CONSTRAINT [PK_Student1] PRIMARY KEY CLUSTERED 
(
    [SNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Student1]  WITH CHECK ADD  CONSTRAINT [FK_Student1_Teacher1] FOREIGN KEY([TNo])
REFERENCES [dbo].[Teacher1] ([TNo])
GO

ALTER TABLE [dbo].[Student1] CHECK CONSTRAINT [FK_Student1_Teacher1]
GO


---insert
INSERT INTO [Student1] VALUES(1,'czm1','u',1)
INSERT INTO [Student1] VALUES(2,'czm2','u',2)
INSERT INTO [Student1] VALUES(3,'czm3','u',3)
INSERT INTO [Student1] VALUES(2,'czm4','u',null)
INSERT INTO [Student1] VALUES(3,'czm5','u',3)

INSERT INTO [Teacher1] VALUES(1,'teacher1','u')
INSERT INTO [Teacher1] VALUES(2,'teacher2','d')
INSERT INTO [Teacher1] VALUES(3,'teacher3','u')


/*******************left join的左右两表的and条件先判断后筛选掉了，再依照主表把值插回去。如果是主表的判断，如student.Sno = '1' 并不是只留下了1行记录，只选择join后的sno=1的次表，然后把其他4个主表的再插回去,而次表显示Null
*******************/
--连接的时候不管主表还是次表都会根据and先筛选出结果(and条件)，再根据主表insert回去
   select * from [Student1] left outer join Teacher1 on Student1.TNo = Teacher1.TNo
   and SName = 'czm2'   --可以看到最后还是5行，但是因为
   --and TName = 'Teacher1'

   --where只有一条数据，最后筛选主表
    select * from [Student1] left outer join Teacher1 on Student1.TNo = Teacher1.TNo
   where SName = 'czm2'


   /***************left outer join是次表带上and条件后和主表join的**************/
   --判断left join   拼次表的时候 是次表带上and条件和主表join 还是次表和主表join后再做and条件判断
      select * from [Student1] left outer join Teacher1 on Student1.TNo = Teacher1.TNo
   and Teacher1.TNo is null--如果是先join的话，czm4那条是有tno=null的，应该会筛选出来。但是结果是次表显示的都是Null,
   --说明先执行的是 次表带上and条件去和主表拼接的。因为次表没有记录tno=null的。

         select * from [Student1] left outer join Teacher1 on Student1.TNo = Teacher1.TNo
   where Teacher1.TNo is null