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

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

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

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

主表条件放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