Category Archives: MySQL

Aug 26 2009

Types of Joins in SQL

A JOIN is a means for combining fields from two tables by using values common to each.
An SQL JOIN clause is used to combine records from two or more tables while querying a database.

All subsequent explanations on join types in this article make use of the following two tables.

Employee Table
LastName DeptID
Rafferty 31
Jones 33
Steinberg 33
Robinson 34
Smith 34
Jasper NULL
Dept Table
DeptID DeptName
31 Sales
33 Engineering
34 Clerical
35 Marketing

 

 

 

Types of Joins :

 

  1. Inner Join :
    1. Equi Join
    2. Natural Join
    3. Self Join
  2. Outer Join :
    1. Left Outer Join
    2. Right Outer Join
    3. Full Outer Join
  3. Cross/Cartesian-Product Join
 
1. Inner Join :
 

  • An inner join creates a new result set by combining column values of two tables (A and B) based upon the join-predicate(ie condition).
  • The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate.
 
1.1 Equi Join :
 

  • An equi-join, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate.

Eg :

Purpose :
To list employees & the departments they belong to.

Method 1 :

SELECT *
FROM employee
INNER JOIN dept
ON employee.deptID = dept.deptID

 
Method 2 :

SELECT *
FROM employee,dept
WHERE employee.deptID = dept.deptID

Result :

Employee.LastName Employee.DeptID Dept.DeptName Dept.DeptID
Robinson 34 Clerical 34
Jones 33 Engineering 33
Smith 34 Clerical 34
Steinberg 33 Engineering 33
Rafferty 31 Sales 31

 

 
1.2 Natural Join :
 

  • The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables.

Eg :

Purpose :
To list employees & the departments they belong to.

Method 1 :

SELECT *
FROM employee
NATURAL JOIN dept

 
Result :

DeptID Employee.LastName Dept.DeptName
34 Smith Clerical
33 Jones Engineering
34 Robinson Clerical
33 Steinberg Engineering
31 Rafferty Sales

 

 
1.3 Self Join :
 

  • Similar in syntax with INNER Join except that A self-join is joining a table to itself.

Eg :

Purpose :
To list employees belonging to the same department.

Method 1 :

SELECT a.DeptID,a.LastName
FROM employee a
INNER JOIN employee b ON (a.deptid = b.deptid AND a.lastname!=b.lastname)
ORDER BY a.deptid

 
Result :

DeptID LastName
33 Jones
33 Steinberg
34 Smith
34 Robinson

 

 
2. Outer Join :
 

  • An outer join does not require each record in the two joined tables to have a matching record.
  • The joined table retains each record—even if no other matching record exists.
 
2.1 Left Outer Join :
 

  • Left outer join(or simply left join) returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).
  • If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table.

Eg :

Purpose :
To show ALL employees of the company along with the departments they are in.

Method :

SELECT *
FROM employee LEFT OUTER JOIN dept
ON employee.DeptID = dept.DeptID

Result :

Employee.LastName Employee.DeptID Dept.DeptName Dept.DeptID
Jones 33 Engineering 33
Rafferty 31 Sales 31
Robinson 34 Clerical 34
Smith 34 Clerical 34
Jasper NULL NULL NULL
Steinberg 33 Engineering 33

 

 
2.2 Right Outer Join :
 

  • A Right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed.
  • A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
  • SQLite does not support right join. Left Join can be used as an alternative here by exchanging the table positions.

Eg :

Purpose :
To show ALL departments alongwith the employees in them.

Method :

SELECT *
FROM employee RIGHT OUTER JOIN dept
ON employee.deptID = dept.deptID

Result :

Employee.LastName Employee.DeptID Dept.DeptName Dept.DeptID
Smith 34 Clerical 34
Jones 33 Engineering 33
Robinson 34 Clerical 34
Steinberg 33 Engineering 33
Rafferty 31 Sales 31
NULL NULL Marketing 35

 

 
2.3 Full Outer Join :
 

  • A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
  • Some database systems (like MySQL) do not support this functionality directly, but they can emulate it through the use of left and right outer joins and unions.

Eg :

Purpose :
To show ALL employees & ALL departments.

Method 1 :

SELECT *
FROM employee
FULL OUTER JOIN dept
ON employee.deptID = dept.deptID

 
Method 2 : Implementing Full Outer Join using Left,Right Joins :

SELECT *
FROM employee
LEFT JOIN dept
ON employee.deptID = dept.deptID
UNION
SELECT *
FROM employee
RIGHT JOIN dept
ON employee.deptID = dept.deptID
WHERE employee.deptID IS NULL

Result :

Employee.LastName Employee.DeptID Dept.DeptName Dept.DeptID
Smith 34 Clerical 34
Jones 33 Engineering 33
Robinson 34 Clerical 34
Jasper NULL NULL NULL
Steinberg 33 Engineering 33
Rafferty 31 Sales 31
NULL NULL Marketing 35

 

 
3. Cross/Cartesian-Product Join :
 

  • A cross join returns the cartesian product of the sets of records from the two joined tables.
  • The cross join does not apply any predicate to filter records from the joined table.
  • Programmers can further filter the results of a cross join by using a WHERE clause.

Eg 1:

SELECT *
FROM employee CROSS JOIN dept

 
Eg 2:

SELECT *
FROM employee, dept;

 

Ref :
http://en.wikipedia.org/wiki/Join_(SQL)

 

 

Aug 16 2009

MySQL Full Text Searching

Full Text Searching is a method of searching data.
In MySQL,  Full Text Searching literally allows us to search for a needle in a haystack in no time !

In order to use this feature, a full text index has to exist on a the given column. In MySQL, this type of index is only permitted on MyISAM tables (expected to change in further versions).

There are 3 modes of FT Searching :

1. Natural Language Search (Default) :

 

The following query searches for the word ‘database’ in the columns ‘title’ or ‘body’
eg :
SELECT * FROM articles WHERE MATCH (title,body)    AGAINST (‘database’ );

2.  Boolean Search :

 

Boolean mode allows us to use complex expressions which can state whether data should contain certain words,whether data shouldn’t contain certain words,whether data should contain a certain phrase etc.

The following query searches for data having the word ‘mysql’ AND
not having the word ‘yoursql’
eg :
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST (‘+MySQL -YourSQL’ IN BOOLEAN MODE);

The following table shows the different types of operators permitted :

Example Action
‘apple banana’ Find rows that contain at least one of the two words.
‘+apple +juice’ Find rows that contain both words.
‘+apple macintosh’ Find rows that contain the word “apple”, but rank rows higher if they also contain “macintosh”.
‘+apple -macintosh’ Find rows that contain the word “apple” but not “macintosh”.
‘+apple ~macintosh’ Find rows that contain the word “apple”, but if the row also contains the word “macintosh”, rate it lower than if row does not. This is “softer” than a search for ‘+apple -macintosh’, for which the presence of “macintosh” causes the row not to be returned at all.
‘+apple +(>turnover <strudel)’ Find rows that contain the words “apple” and “turnover”, or “apple” and “strudel” (in any order), but rank “apple turnover” higher than “apple strudel”.
‘apple*’ Find rows that contain words such as “apple”, “apples”, “applesauce”, or “applet”.
‘”some words”‘ Find rows that contain the exact phrase “some words” (for example, rows that contain “some words of wisdom” but not “some noise words”). Note that the “”” characters that enclose the phrase are operator characters that delimit the phrase. They are not the quotes that enclose the search string itself.

 

Some Rules of Boolean Search :

  • Zero rows are returned if word searched is less than the minimum word length defined in the variable ft_min_word_len in the cnf file of MySQL (default is 4 letters).
  • 50% threshold rule  doesnt apply here.
  • The stopword list applies. It contains common words (like ‘in’,'of’ etc) which are ignored while searching.
3. Query Expansion :

 

This is for the user who relies on implied knowledge to get the desired result. For example, a user searching for “database” may really mean that “MySQL”, “Oracle”, “DB2”, and “RDBMS” all are phrases that should match “databases” and should be returned, too. This is implied knowledge.
eg :
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST (‘database’ WITH QUERY EXPANSION);

Ref :
MySQL – Full Text Boolean Search
MySQL – FT Search with Query Expansion
FT Searching Slideshow
New Features in FT Searching Slideshow
Preetul – How to Change the full text index minimum word length

Aug 16 2009

MySQL Table Types

When I started out , I was made aware of only a couple of table types (now called ‘storage engines‘) in MySQL since they were used frequently. At some point of time my curiousity grew & I learnt that MySQL offers a whole variety (though they are for highly specialized uses only!).So here goes.

The 10 MySQL Storage Engines :

1. MyISAM

  • Default storage engine.
  • Supports Full Text Indexing.
  • Table level locking.

2. InnoDB

  • Row level locking.
  • Transaction-safe (ACID compliant) storage engine.
  • Occupies more space than MyISAM tables.
  • Used in sites having large data while ensuring high performance.

3. Memory/Heap

  • Memory tables are storied in memory.
  • Use of hash indexes by default making them very fast.
  • Useful only for temporary tables since server shutdown causes loss of all data.

4. Merge/MRG_MyISAM

  • Collection of identical MyISAM tables.
  • Identical means that the underlying tables should have same columns,indexes,same order of columns/indexes,column type etc.
  • Useful for speed,reparing,managing data in big tables  broken into smaller parts & ‘merged’ .
  • Reads from the merged table are slower than individual reads.

5. Archived

  • Useful for storing large amounts of data  in a very small footprint since rows are compressed.
  • Indexing is not supported.
  • Reading is slow since a complete table scan occurs.

6. Federated

  • Used to access data from a remote MySQL database on a local server without using replication or cluster technology.

7. Blackhole

  • It acts as a ‘black hole‘  that accepts data but throws it away and does not store it.
  • Useful for Master-Slave configurated servers  to reduce the amount of data being transferred between master and possible many slaves.
  • Used to check the query syntax alone while not touching the physical records of the mysql engine.

8. CSV

  • Storage of  data in text files using comma-separated values format.
  • Indexing is not supported.
  • This format can be read, and even written, by spreadsheet applications such as Microsoft Excel or StarOffice Calc.

9. Example

  • Stub engine that does nothing.
  • Useful for developers who wish to learn how to begin writing new storage engines.
  • Indexing is not supported.

10. NDBCLUSTER

  • Used to implement tables that are partitioned over many computers.

note : The storage engines ISAM & BDB(Berkeley DB) are deprecated now.

Ref :
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
Daniel Schneller -  Explanation of Blackhole Storage Engine