Introduction

• Sometimes we need an information which is receiving data from multiple tables.
• If the tables have some common field then it become easier to join these tables and can retrieve the data.
• In this chapter, we will learn joining of tables and the process of information retrieval.
• In this chapter we will also learn indexes of SQL which facilitates database processing.

Joins

• Join is a query which combine rows of two or more tables.
• In a join-query, we need to provide a list of tables in FROM Clause.
• The process of combining multiple tables in order to retrieve data is called joining. For ex-
  SELECT * FROM emp1, dept;
• Unrestricted join or cartesian product of both the tables gives all possible concatenations of all the rows of both the tables.

Making Table ready for join

Making Table ready for join

Join query

Join query

Additional search with Join Query

Using Table Aliases

Joining more than one table

Joining table using Join Clause

LEFT-JOIN

• When we use LEFT-JOIN, it returns all rows from first table whether it has matching rows in second table or not.
• It shows NULL in columns for the unmatched rows of first table.

mysql>SELECT <Col List> FROM <table1> LEFT JOIN <table2>
                                      ON <joining Condition>

Right-JOIN

• When we use RIGHT-JOIN, it returns all rows from second table whether it has matching rows in first table or not.
• It shows NULL in columns for the unmatched rows of second table.

mysql>SELECT <Col List> FROM <table1> RIGHT JOIN <table2>
                                        ON <joining Condition>

Indexes in Database

• Index is a data structure maintained by a database that helps to find records within a table more quickly.
• An index stores the sorted/ordered values within the index field and their location in the actual table.
• An index in a database is also a table which stores arranged values of one or more columns in a specific order.

Creation of Indexes in MySQL

Creation of Indexes in MySQL

Indexes in Database

Advantages & Disadvantages of Indexes

• Advantages :
• With Indexes, queries gives much better performance.
• Data retrieval is much faster with Indexes.
• Indexes are very useful for Sorting purpose.
• Unique indexes guarantee uniquely identifiable records in the database.

• Disadvantages :
• With Indexes, the performance of insert, update and delete decreases. As every time insert/update/delete operation happens, the index is to be updated accordingly.
• Index consumes storage space and this increases with the number of fields used and the length of the table.
• Thus, it is advised that one should only create indexes only when actually needed.