what is SQL UNION clause?

The SQL UNION clause

The SQL UNION clause is used to combine the results of two or more SELECT statements without returning any duplicate rows.

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length.

Syntax:

The basic syntax of UNION is as follows:

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

UNION

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

The UNION ALL Clause:

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

There are two other clauses (i.e., operators), which are very similar to UNION clause:

·        SQL INTERSECT Clause: is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.

·        SQL EXCEPT Clause : combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.

What is Self-Join in SQL

What is Self-Join in SQL?

A self-join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table it-self means that each row of the table is combined with itself and with every other row of the table.

The self-join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have same name. Table name aliases are defined in the FROM clause of the SELECT statement. See the syntax:

SELECT a.column_name, b.column_name…..

FROM table1 a, table1 b

WHERE a.common_filed = b.common_field;

Example of SQL SELF JOIN

In the following example we will use the table EMPLOYEE twice and in order to do this we will use the alias of the table.

To get the list of employees and their Manager the following sql statement has used:

SELECT a.emp_id AS "Emp_ID",a.emp_name AS "EmployeeName", 

b.emp_id AS "Manager ID",b.emp_name AS "ManagerName" 

FROM employee a, employee b 

WHERE a.emp_supv = b.emp_id;

CAST Function in SQL

CAST Function

In many cases, a database will automatically convert one data type into another when needed. On the other hand, there are instances when that is not the case, or when you want to explicitly specify what data type to change into. In these cases, you can use the CAST function. The syntax of the CAST function is as follows:

CAST (expression AS [data type])

where [data type] is a valid data type in the RDBMS you are working with.

Table Student

StudentID	First_Name	Marks
1	Anil	95.9
2	Sunil	93.5
3	Ram	80
4	sumit	70.1

Example 

SELECT First_Name, CAST(Marks AS Integer) marks FROM Student_Score;

OutPut:

First_Name	Int_Marks
Anil	96
Sunil	94
Ram	80
sumit	70

CAST in SQL

CAST Function

In many cases, a database will automatically convert one data type into another when needed. On the other hand, there are instances when that is not the case, or when you want to explicitly specify what data type to change into. In these cases, you can use the CAST function. The syntax of the CAST function is as follows:

CAST (expression AS [data type])

where [data type] is a valid data type in the RDBMS you are working with.

Table Student

StudentID	First_Name	Marks
1	Anil	95.9
2	Sunil	93.5
3	Ram	80
4	sumit	70.1

Example 

SELECT First_Name, CAST(Marks AS Integer) marks FROM Student_Score;

OutPut:

First_Name	Int_Marks
Anil	96
Sunil	94
Ram	80
sumit	70

SQL Server CONVERT() Function

SQL Server CONVERT() Function

The CONVERT() function is a general function that converts an expression of one data type to another.

The CONVERT() function can be used to display date/time data in different formats.

Syntax

CONVERT(data_type(length),expression,style)

Value	Description
data_type(length)	Specifies the target data type (with an optional length)
expression	Specifies the value to be converted
style	Specifies the output format for the date/time (see table below)

Example

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

The result would look something like this:

Nov 04 2014 11:45 PM
11-04-14
11-04-2014
04 Nov 14
04 Nov 2014
04 Nov 2014 11:45:34:243