Indexes help to speed up database search. Without index a full table scan needed which is slow. B-Tree is the data structure behind indexes. It is efficient for lookups, insertions and deletions.All operations done in logarithmic time.Data store in B-Tree can be sorted.
CREATE INDEX index_name table_name column_name
| JOINS | Description | Query |
|---|---|---|
| Inner Join | Selects records that have matching values in both tables. It returns only the rows where there is a match in both tables. | SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column; |
| Left Join | Returns all records from the left table (table1), and the matched records from the right table (table2). If there is no match, the result is NULL on the right side. | SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column; |
| Right Join | Returns all records from the right table (table2), and the matched records from the left table (table1). If there is no match, the result is NULL on the left side. | SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; |
| Full Outer Join | Returns all records when there is a match in either left (table1) or right (table2) table records. If there is no match, the missing side will contain NULL. | SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column; |
| Cross Join | Returns the Cartesian product of the two tables, i.e., it combines each row of the first table with each row of the second table. | SELECT columns FROM table1 CROSS JOIN table2; |