MySql Function Between.. AND in LEFT JOIN

MySql Function Between.. AND in LEFT JOIN

If we are storing range of values in a field in a table and we want to see what are the values falling between these range, we can use "Between.. and" function along with Left Join in Mysql.

Below is the an example to explain it in detail:

1) Table : salary

Â Salary table holds fields like id, name and salary.Â Â

Â Structure:

CREATE TABLE IF NOT EXISTS `salary` ( `id` int(11) NOT NULL,Â  `name` varchar(25) NOT NULL,`salary` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert some test values:

INSERT INTO `salary` (`id`, `name`, `salary`) VALUES (1, 'A', 2000),(2, 'B', 3000),(3, 'C', 4000),(4, 'D', 3000),(5, 'E', 2000),(6, 'F', 5000);

Â 2) Table : grade

Â grade table holds fields like class (holds class name for each grade),lower (lower limit of salary for a particular grade), upper (upper limit of salary for particular grade)Â Â

CREATE TABLE IF NOT EXISTS `grade` ( `class` varchar(2) NOT NULL,`lower` int(11) NOT NULL,Â  `upper` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert some test values:

INSERT INTO `grade` (`class`, `lower`, `upper`) VALUES ('A1', 2000, 3000),('A2', 3001, 4000),('A3', 4001, 5000),('A4', 5001, 6000);

Â Run below query you will get class for each employee as per their salary.

SELECTÂ salaryÂ .Â *Â ,Â gradeÂ .Â * FROMÂ `salary`Â 
  LEFTÂ JOINÂ gradeÂ ONÂ `salary` BETWEENÂ lower ANDÂ upper

id	name	salary	class	lower	upper
1	A	2000	A1	2000	3000
2	B	3000	A1	2000	3000
3	C	4000	A2	3001	4000
4	D	3000	A1	2000	3000
5	E	2000	A1	2000	3000
6	F	5000	A3	4001	5000

[si-contact-form form='1']

Your feedbacks are most welcome..