CLASS -11 COMPUTER SCIENCE
CHAPTER- 19 TABLE JOINS AND INDEXES IN SQL
🔔To download textbook pdf click on the ''DOWNLOAD'' button below -
NOTES FOR COMPLETE UNDERSTANDING -
- 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.
- Unrestricted join or cartesian product of both the tables gives all possible concatenations of all the rows of both the tables.
- How to JOIN ?
- Using table aliases :
- A table alias is a temporary label given along with table name in FROM clause .
- Consider two relations as below : ( !! THESE TWO WILL BE USED LATER ALSO !! )
- Using another search conditions :
- Example -
- Types of JOINS for joining more than two tables :
- Equi-Join : The join , in which columns are compared for equality is called Equi- Join .
- Non-Equi Join : It is a query that specifies some relationship other than the equality between the columns .
- Natural Join : The join in which only one of the identical columns exists is called Natural Join .
- Types of JOIN Clause of SQL Select :
- Cross Join : The cross join is a very basic type of join that simply matches each row from one table to every row of the other table .
- Natural Join : same as above
- Left Join : When we use Left Join all rows from the first table will be returned whether they match or not to the second table . For unmatched rows of the first table NULL is shown in the columns of second table .
- Right Join : This also works in the same manner as done by Left Join but with the table order reversed .
- 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 :
- We can create indexes in MySQL by two methods -
- At the time of table creation.
- Creation of index at some already existing table.
- 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.
🔔MORE MATERIALS TO BE UPLOADED STAY TUNED🔔
