Conditional link for T-SQL

--  Demo data  If   Object_id ( '  Tempdb .. #  ' ) Is   Not   Null      Drop   Table  #  Go  Create   Table  # A (a_id Int  , A_col  Int  )  Insert  #  Select   1 , 1   Union   All  Select   2 , Null   Union   All Select   3 , 1   Union   All  Select   3 , 2   Union   All  Select   4 , 1  If   Object_id ('  Tempdb .. # B  ' ) Is   Not   Null      Drop   Table  #  Go  Create   Table  # B (B _id  Int , B _col Int  )  Insert  Into  # B  Select   1 , 1   Union   All  Select   2 , Null   Union   All  Select   Null , 1   /* 1. Join (inner join) Inner can omit matching the records in one table with the records in another table, provided that the related columns of the two tables contain the same value. If one of the table's column values is different or there is no value at all, the query will not return these rows. Perform Cartesian product on the two tables and then filter the conditions based on. The same as from a, B where a. ID = B. ID is equivalent join.  */  --  Inner join  Select   *   From # As A Inner   Join # B As B On A. A _ id=  B. B _ id  --  There is no difference between the following two query results  Select   *   From # As A Inner   Join # B As B On A. A _ id = B. B _ id And A. A _ col =  1   Select   *   From # As A Inner   Join # B As B On A. A _ id = B. B _ id Where A. A _ col =   1           /* 2. left Outer Join: The result set of the left Outer Join includes all rows in the left table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all the selection lists in the right table in the associated result set are null.  */      --  Left join  Select   *   From # As A Left   Join # B As B On A. A _ id =  B. B _ id Select   *   From # As A Left   Join # B As B On A. A _ col =   1   --  That is to say, we add a non-1 cross join operation of 1.  --  Differences between the following two statements  Select  *   From # As A Left   Join # B As B On A. A _ col =   1   Where B. B _ col =   1       Select   *   From #As A Left   Join # B As B On A. A _ col =   1   And B. B _ col =   1       /*  3. Right join (right Outer Join) the right outer join is the reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left and right tables.  */  -- Right join  Select   *   From # As A Right   Join # B As B On A. A _ id =  B. B _ id  Select   *   From # As ARight   Join # B As B On A. A _ id = B. B _ id And A. A _ col =   1   Select   *   From # As A Right   Join # B As B On A. A _ id = B. B _ id And A. A _ col =   1  Where B. B _ col <>   1   Select   *   From # As A Right   Join # BAs B On A. A _ col =   1   /*  4. Full outer join (full join or full outer join) the complete Outer Join returns all rows in the left and right tables. When a row does not match in another table, the selection list of another table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table. For a complete external join, you can understand its output result as follows: add all rows in Table B that are not displayed in the result to the result set, for the first row added to the result set, the value of column A in table should be replaced by null.  */      Select   *   From # As AFull   Join # B As B On A. A _ id =  B. B _ id  Select   *   From # As A Full   Join # B As B On A. A _ id =  1  /*  5. Cross join returns all rows in the left table. The first row in the left table is combined with all rows in the right table. Cross join is also called Cartesian product.  */  --  Cross join  Select   *   From # As A Cross   Join # B As B