SQL - UNION CLAUSE

UNIONS CLAUSE:

·         The SQL UNION clause/operator 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:

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

UNION

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

EXAMPLE: CUSTOMER TABLE

Table: orders

SELECT c.customerid, firstname, totalamount, orderdate

     FROM customer c

     LEFT JOIN orders o

     ON c.customerid = o.customerid

UNION

    SELECT c.customerid, firstname, totalamount, orderdate

     FROM customer c

    RIGHT JOIN orders o

     ON c.customerid = o.customerid

UNION ALL Clause:

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

Syntax:

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Example:

Consider the following two tables, (a) CUSTOMERS table and ORDER table.

SELECT c.customerid, firstname, totalamount, orderdate

     FROM customer c

     LEFT JOIN orders o

     ON c.customerid = o.customerid

UNION ALL

    SELECT c.customerid, firstname, totalamount, orderdate

     FROM customer c

    RIGHT JOIN orders o

     ON c.customerid = o.customerid

Two types of Union clauses:

1. INTERSECT Clause:

The SQL INTERSECT clause/operator 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. This means INTERSECT returns only common rows returned by the two SELECT statements.

Syntax:

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

INTERSECT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Here given condition could be any given expression based on your requirement.

Example:

SELECT c.customerid, firstname, totalamount, orderdate

     FROM customer c

     LEFT JOIN orders o

     ON c.customerid = o.customerid

INTERSECT

    SELECT c.customerid, firstname, totalamount, orderdate

     FROM customer c

    RIGHT JOIN orders o

     ON c.customerid = o.customerid

2. EXCEPT Clause:

 The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in second SELECT statement.

Syntax:

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Here given condition could be any given expression based on your requirement.

Example:

Consider the following two tables,  CUSTOMERS table and ORDER table is as follows:

SELECT c.customerid, firstname, totalamount, orderdate

     FROM customer c

     LEFT JOIN orders o

     ON c.customerid = o.customerid

EXCEPT

    SELECT c.customerid, firstname, totalamount, orderdate

     FROM customer c

    RIGHT JOIN orders o

     ON c.customerid = o.customerid

SQL - INDEX & VIEWS

INDEXES:

·         Indexes are special lookup tables that the database search engine can use to speed up data retrieval.

·         Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

·         An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

The CREATE INDEX Command:

The basic syntax of CREATE INDEX is as follows:

CREATE INDEX index_name ON table_name;

 TYPES OF INDEXES:

1. Single-Column Indexes:

A single-column index is one that is created based on only one table column.

CREATE INDEX index_name

ON table_name (column_name);

2. Unique Indexes:

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table.

CREATE UNIQUE INDEX index_name

on table_name (column_name);

3. Composite Indexes:

A composite index is an index on two or more columns of a table.

CREATE INDEX index_name

on table_name (column1, column2);

4. Implicit Indexes:

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

The DROP INDEX Command:

An index can be dropped using SQL DROP command.

DROP INDEX index_name;

When should indexes be avoided?

·      Indexes should not be used on small tables.

·      Tables that have frequent, large batch update or insert operations.

·      Indexes should not be used on columns that contain a high number of NULL values.

·      Columns that are frequently manipulated should not be indexed.

Views:

A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.

Views, which are kind of virtual tables, allow users to do the following:

·      Structure data in a way that users or classes of users find natural or intuitive.

·      Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.

·      Summarize data from various tables which can be used to generate reports.

Creating Views:

Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view.

To create a view, a user must have the appropriate system privilege according to the specific implementation.

CREATE VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

You can include multiple tables in your SELECT statement in very similar way as you use them in normal SQL SELECT query.

Example:

Consider the CUSTOMERS table having the following records

CREATE VIEW customer_view AS

SELECT lastname, city

FROM customer;

select * from customer_view;

SQL - HAVING, WHERE CLAUSE & ALIAS

HAVING clause:

·         The HAVING clause enables you to specify conditions that filter which group results appear in the final results.

·         The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax:

The following is the position of the HAVING clause in a query:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used.

SELECT column1, column2

FROM table1, table2

WHERE [ conditions ]

GROUP BY column1, column2

HAVING [ conditions ]

ORDER BY column1, column2

Example:

Following is the example, which would display record for which similar age count would be more than or equal to 2:

SELECT ID, NAME, AGE, ADDRESS, SALARY

FROM CUSTOMERS

GROUP BY age

HAVING COUNT(age) >= 2;

WHERE CLAUSE:

·         Where clause is used to specify a condition when fetching the data.

·         If the given condition is satisfied then only it returns specific value from the table. You would use WHERE clause to filter the records and fetching only necessary records.

·         The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would examine in subsequent chapters.

Syntax:

SELECT column1, column2, columnN

FROM table_name

WHERE [condition]

SELECT ID, NAME, SALARY

FROM CUSTOMERS

WHERE SALARY > 2000;

Alias Syntax:

·         You can rename a table or a column temporarily by giving another name known as alias.

·         The use of table aliases means to rename a table in a particular SQL statement.

·         The renaming is a temporary change and the actual table name does not change in the database.

Syntax:

SELECT column1, column2....

FROM table_name AS alias_name

WHERE [condition];

SELECT column_name AS alias_name

FROM table_name

WHERE [condition];

Example:

Consider the following two tables, CUSTOMERS table AND ORDERS table is as follows:

Now, following is the usage of table alias:

SELECT c.customerid, C.firstname, O.totalamount, O.orderdate

 FROM customer AS c, orders AS o

WHERE C.customerid=o.customerid

Following is the usage of column alias:


SELECT  customerID AS CUSTOMER_ID, firstNAME AS 

CUSTOMER_firstNAME

 FROM CUSTOMER


 WHERE city IS NOT NULL;