SQL*PLUS

JYOTI

ASSISTANT PROFESSOR

COMPUTER SCIENCE & IT

🙢

INTRODUCTION:

• The SQL Joins clause is used to combine records from two or more tables in a database.
• A JOIN is a means for combining fields from two tables by using values common to each.

🙢

Table 1 − CUSTOMERS Table�

​

• +----+----------+-----+-----------+----------+
• | ID | NAME | AGE | ADDRESS | SALARY|
• +----+----------+-----+-----------+----------+
• | 1 | Ramesh | 32 | Ahmedabad | 2000.00|
• | 2 | Khilan | 25 | Delhi | 1500.00 |
• | 3 | kaushik | 23 | Kota | 2000.00 |
• | 4 | Chaitali | 25 | Mumbai | 6500.00 |
• | 5 | Hardik | 27 | Bhopal | 8500.00 |
• | 6 | Komal | 22 | MP | 4500.00 |
• | 7 | Muffy | 24 | Indore | 10000.00|
• +----+----------+-----+-----------+----------+

🙢

Table 2 − ORDERS Table

• +-----+---------------------+-------------+--------+
• |OID | DATE | CUSTOMER_ID | AMOUNT |
• +-----+---------------------+-------------+--------+
• | 102 | 2009-10-08 00:00:00 | 3 | 3000 |
• | 100 | 2009-10-08 00:00:00 | 3 | 1500 |
• | 101 | 2009-11-20 00:00:00 | 2 | 1560 |
• | 103 | 2008-05-20 00:00:00 | 4 | 2060 |
• +-----+---------------------+-------------+--------+

🙢

���SQL> SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS, ORDERS WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;This would produce the following result.�

• +----+----------+-----+--------+
• | ID | NAME | AGE | AMOUNT |
• +----+----------+-----+--------+
• | 3 | kaushik | 23 | 3000 |
• | 3 | kaushik | 23 | 1500 |
• | 2 | Khilan | 25 | 1560 |
• | 4 | Chaitali | 25 | 2060 |
• +----+----------+-----+--------+

🙢

• It is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal to symbol.
• There are different types of joins available in SQL −
• INNER JOIN − returns rows when there is a match in both tables.
• LEFT JOIN − returns all rows from the left table, even if there are no matches in the right table.
• RIGHT JOIN − returns all rows from the right table, even if there are no matches in the left table.
• FULL JOIN − returns rows when there is a match in one of the tables.
• SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
• CARTESIAN JOIN − returns the Cartesian product of the sets of records from the two or more joined tables.

​

🙢

SQL UNION

• SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.
• To use this UNION clause, each SELECT statement 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

​

🙢

• Syntax
• The basic syntax of a UNION clause is as follows −
• SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

🙢

�Example:�Consider the following two tables.�Table 1 − CUSTOMERS Table is as follows.�

• +----+----------+-----+-----------+----------+
• | ID | NAME | AGE | ADDRESS | SALARY |
• +----+----------+-----+-----------+----------+
• | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
• | 2 | Khilan | 25 | Delhi | 1500.00 |
• | 3 | kaushik | 23 | Kota | 2000.00 |
• | 4 | Chaitali | 25 | Mumbai | 6500.00 |
• | 5 | Hardik | 27 | Bhopal | 8500.00 |
• | 6 | Komal | 22 | MP | 4500.00 |
• | 7 | Muffy | 24 | Indore | 10000.00 |
• +----+----------+-----+-----------+----------+

🙢

Table 2 − ORDERS Table is as follows

• |OID | DATE | CUSTOMER_ID | AMOUNT |
• +-----+---------------------+-------------+--------+
• | 102 | 2009-10-08 00:00:00 | 3 | 3000 |
• | 100 | 2009-10-08 00:00:00 | 3 | 1500 |
• | 101 | 2009-11-20 00:00:00 | 2 | 1560 |
• | 103 | 2008-05-20 00:00:00 | 4 | 2060 |
• +-----+---------------------+-------------+--------+

🙢

The UNION ALL Clause�

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

• The same rules that apply to the UNION clause will apply to the UNION ALL operator.
• Syntax
• The basic syntax of the UNION ALL is as follows.
• SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

🙢

SQL INTERSECT

• 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.
• Just as with the UNION operator, the same rules apply when using the INTERSECT operator. MySQL does not support the INTERSECT operator.

​

🙢

Syntax

• The basic syntax of INTERSECT is as follows.
• SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
• INTERSECT SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

🙢

Table 1 − CUSTOMERS Table is as follows

• +----+----------+-----+-----------+----------+
• | ID | NAME | AGE | ADDRESS | SALARY |
• +----+----------+-----+-----------+----------+
• | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
• | 2 | Khilan | 25 | Delhi | 1500.00 |
• | 3 | kaushik | 23 | Kota | 2000.00 |
• | 4 | Chaitali | 25 | Mumbai | 6500.00 |
• | 5 | Hardik | 27 | Bhopal | 8500.00 |
• | 6 | Komal | 22 | MP | 4500.00 |
• | 7 | Muffy | 24 | Indore | 10000.00 |
• +----+----------+-----+-----------+----------+

🙢

Table 2 − ORDERS Table is as follows.

• +-----+---------------------+-------------+--------+
• |OID | DATE | CUSTOMER_ID | AMOUNT |
• +-----+---------------------+-------------+--------+
• | 102 | 2009-10-08 00:00:00 | 3 | 3000 |
• | 100 | 2009-10-08 00:00:00 | 3 | 1500 |
• | 101 | 2009-11-20 00:00:00 | 2 | 1560 |
• | 103 | 2008-05-20 00:00:00 | 4 | 2060 |
• +-----+---------------------+-------------+--------+

🙢

• Let us join these two tables in our SELECT statement as follows.
• SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID INTERSECT SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

🙢

SQL – an example of a tree-walking�

🙢

is stored like this in database table:

🙢

SQL INDEXING

• An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.
• Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.

​

🙢

The CREATE INDEX Command�

• The basic syntax of a CREATE INDEX is as follows.
• CREATE INDEX index_name ON table_name; Single-Column Indexes
• A single-column index is created based on only one table column. The basic syntax is as follows.
• CREATE INDEX index_name ON table_name (column_name);

🙢

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. The basic syntax is as follows.
• CREATE UNIQUE INDEX index_name on table_name (column_name);

🙢

Composite Indexes�

• A composite index is an index on two or more columns of a table. Its basic syntax is as follows.
• CREATE INDEX index_name on table_name (column1, column2);

🙢

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.

​

🙢

When should indexes be avoided?�

• Although indexes are intended to enhance a database's performance, there are times when they should be avoided.
• The following guidelines indicate when the use of an index should be reconsidered.
• Indexes should not be used on small tables.
• Tables that have frequent, large batch updates 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.

​

🙢

THANK YOU�

🙢