Tag: mysql

Clustered Indexes v/s Non-Clustered Indexes

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and the use of more storage space. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records.

There are basically two types of indexes: Clustered Indexes and Non-Clustered Indexes. Both uses “B-TREE” for searching data. However, storage of physical data differs.

  •  Clustered Indexes:  In clustered index, the non-leaf level points to the actual data.
Clustered Indexes
Clustered Index Architecture
  • Non-Clustered Indexes: In Non-Clustered index the leaf nodes point to pointers, which then point to actual data.
Non-Clustered Indexes
Non-Clustered Index Architecture

 

 

Visual Explanation of SQL Joins

An SQL join clause combines records from two or more tables in a database. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT. Since SQL joins appear to be set-based, its easy to explain using Venn diagrams.

LEFT OUTER JOIN: produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

RIGHT OUTER JOIN: produces a complete set of records from Table B, with the matching records (where available) in Table A. If there is no match, the left side will contain null.

FULL OUTER JOIN: produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

INNER JOIN: produces only the set of records that match in both Table A and Table B.