Pre-Launch Singapore FireWork 2008

Wonderful!!! This movie was taken last night which it pre-launch of firework for Singapore National Day 2008.


Read more...

Simple SQL Table Join

I would like to share about the simple join in my web page. The page is very draft but I hope it will be a useful information for a beginner of T-SQL.

TABLE JOIN

Create Test Table

USE [TESTDB]
GO
/****** Object: Table [Test] Script Date: 07/08/2008 11:46:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Test](
[col1] [varchar](5) NULL,
[col2] [varchar](5) NULL,
[col3] [varchar](5) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


Create Test1 Table

USE [TESTDB]
GO
/****** Object: Table [Test1] Script Date: 07/08/2008 11:45:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Test1](
[ID] [varchar](5) NOT NULL,
[total] [smallint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

GO
SELECT * FROM TEST

Result
===========================
col1 col2 col3
1 AA dd
2 bb ee
8 NULL NULL


GO
SELECT * FROM TEST1

Result
========================
ID total
1 100
2 200
6 150


INNER JOIN RESULT With Test and Test1 Tables
Return the rows from both tables which match the On clause condition

SELECT * FROM TEST1 a INNER JOIN TEST b on a.ID = b.col1

Result
=============================

ID total col1 col2 col3
1 100 1 AA dd
2 200 2 bb ee


LEFT JOIN RESULT

The result contain all rows from the left table and match rows from the right table.

SELECT * FROM TEST1 a LEFT JOIN TEST b on a.ID = b.col1

Result
=============================

ID total col1 col2 col3
1 100 1 AA dd
2 200 2 bb ee
6 150 NULL NULL NULL



RIGHT JOIN RESULT

The result contains all rows from the right table and match rows from the left table.

SELECT * FROM TEST1 a RIGHT JOIN TEST b on a.ID = b.col1

Result
=============================

ID total col1 col2 col3
1 100 1 AA dd
2 200 2 bb ee
NULL NULL 8 NULL NULL


LEFT OUTER JOIN RESULT

Same with left outer join.

SELECT * FROM TEST1 a LEFT OUTER JOIN TEST b on a.ID = b.col1

Result
=============================

ID total col1 col2 col3
1 100 1 AA dd
2 200 2 bb ee
6 150 NULL NULL NULL


RIGHT OUTER JOIN RESULT

Same with right join.

SELECT * FROM TEST1 a RIGHT OUTER JOIN TEST b on a.ID = b.col1

Result
=============================

ID total col1 col2 col3
1 100 1 AA dd
2 200 2 bb ee
NULL NULL 8 NULL NULL

FULL OUTER JOIN RESULT

The result contains all the rows from the left table and all the rows from the right table.

SELECT * FROM TEST1 a FULL OUTER JOIN TEST b on a.ID = b.col1

Result
=============================

ID total col1 col2 col3
1 100 1 AA dd
2 200 2 bb ee
6 150 NULL NULL NULL
NULL NULL 8 NULL NULL

CROSS JOIN RESULT

The result contains every row in first table is
joins with every row in second table without having any condition in the cross join.

SELECT * FROM TEST1 CROSS JOIN TEST

Result
=============================
ID total col1 col2 col3
1 100 1 AA dd
2 200 1 AA dd
6 150 1 AA dd
1 100 2 bb ee
2 200 2 bb ee
6 150 2 bb ee
1 100 8 NULL NULL
2 200 8 NULL NULL
6 150 8 NULL NULL
Read more...