Difference between inner join and outer join

Assuming you're joining on columns with no duplicates, which is by far the most common case:

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection.
• An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union.

Examples
Suppose you have two Tables, with a single column each, and data as follows:

A    B
-    -1    32    43    54    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;select a.*,b.*  from a,b where a.a = b.b;

a | b
--+--3 | 34 | 4

Left outer join
A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;select a.*,b.*  from a,b where a.a = b.b(+);

a |  b  
--+-----1 | null2 | null3 |    34 |    4

Full outer join
A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b  
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4null |    6null |    5

Removing Leading Zeros From Column in Table

Improving the ResultSet

I had missed including all zeros in my sample set which was an overlook. Here is the new sample which includes all zero values as well.

USE tempdb
GO-- Create sample tableCREATE TABLE Table1 (Col1 VARCHAR(100))INSERT INTO Table1 (Col1)SELECT '0001'UNION ALLSELECT '000100'UNION ALLSELECT '100100'UNION ALLSELECT '000 0001'UNION ALLSELECT '00.001'UNION ALLSELECT '01.001'UNION ALLSELECT '0000'

Result :

SELECTCASE PATINDEX('%[^0 ]%', Col1 + ' ‘')WHEN 0 THEN ''ELSE SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))END
FROM Table1