Queries
Selecting specific columns
Running against the employee table from Commands.
select code, name, salary from employee;+------+---------------+-----------+
| code | name | salary |
+------+---------------+-----------+
| 101 | Om Sharma | 50000.00 |
| 104 | Devansh | 31000.00 |
| 105 | Shubham | 61000.00 |
| 106 | Atulk | 80000.00 |
| 107 | Anand | 90000.00 |
| 111 | Chinmayee | 100000.00 |
| 201 | Devesh Sharma | 75000.00 |
| 301 | Arnav | 44000.00 |
+------+---------------+-----------+
8 rows in set (0.00 sec)- Naming columns instead of using
*returns only those columns, in the exact order they're listed,code, thenname, thensalaryhere, not the order they were defined in the table. - Rows still come back sorted by
code, same asSELECT *, that ordering comes from the primary key, not from which columns happen to be selected.
Filtering on multiple conditions with AND
select * from employee where state='up' and gender='m';+------+---------------+---------------+----------+--------+------------+-------+--------+
| code | name | desig | salary | mob | doj | state | gender |
+------+---------------+---------------+----------+--------+------------+-------+--------+
| 201 | Devesh Sharma | Co-Manager | 75000.00 | 2345 | 2020-12-23 | UP | M |
| 301 | Arnav | Asst. Manager | 44000.00 | 123456 | 2020-12-23 | UP | M |
+------+---------------+---------------+----------+--------+------------+-------+--------+
2 rows in set (0.00 sec)ANDnarrows results down to rows matching every condition, not just one, only employees who are both in'UP'and'M'make it through.'up'and'm'were typed lowercase, but matched against'UP'and'M'stored in the table anyway,CHAR/VARCHARcomparisons in MySQL are case-insensitive by default.
A numeric range with AND
select * from employee where salary>20000 and salary<=80000;+------+---------------+---------------+----------+-----------+------------+-------------+--------+
| code | name | desig | salary | mob | doj | state | gender |
+------+---------------+---------------+----------+-----------+------------+-------------+--------+
| 101 | Om Sharma | Manager | 50000.00 | 12345 | 2020-12-23 | Uttarakhand | M |
| 104 | Devansh | Clerk | 31000.00 | 1234567 | 2023-04-12 | TN | F |
| 105 | Shubham | Asst. Clerk | 61000.00 | 12345678 | 2026-04-01 | MH | F |
| 106 | Atulk | Clerk | 80000.00 | 123456780 | 2019-07-19 | Goa | M |
| 201 | Devesh Sharma | Co-Manager | 75000.00 | 2345 | 2020-12-23 | UP | M |
| 301 | Arnav | Asst. Manager | 44000.00 | 123456 | 2020-12-23 | UP | M |
+------+---------------+---------------+----------+-----------+------------+-------------+--------+
6 rows in set (0.00 sec)Same AND as before, just two conditions on the same column instead of two different columns, salary > 20000 and salary <= 80000 together define a range. 106 (80000.00) makes the cut since <= includes the boundary; 107 (90000.00) and 111 (100000.00) don't, both over the upper bound.
The same range, with BETWEEN
select * from employee where salary between 20000 and 50000;+------+-----------+---------------+----------+---------+------------+-------------+--------+
| code | name | desig | salary | mob | doj | state | gender |
+------+-----------+---------------+----------+---------+------------+-------------+--------+
| 101 | Om Sharma | Manager | 50000.00 | 12345 | 2020-12-23 | Uttarakhand | M |
| 104 | Devansh | Clerk | 31000.00 | 1234567 | 2023-04-12 | TN | F |
| 301 | Arnav | Asst. Manager | 44000.00 | 123456 | 2020-12-23 | UP | M |
+------+-----------+---------------+----------+---------+------------+-------------+--------+
3 rows in set (0.01 sec)BETWEEN 20000 AND 50000 is shorthand for salary >= 20000 AND salary <= 50000, both ends inclusive. Worth noting the bound moved down to 50000 here versus 80000 in the section above, that's a different range being asked for, not a difference in how BETWEEN behaves, 101 at exactly 50000.00 is included precisely because BETWEEN includes its upper bound too.
Inverting it with NOT BETWEEN
select * from employee where salary not between 20000 and 50000;+------+---------------+---------------+-----------+------------+------------+-------+--------+
| code | name | desig | salary | mob | doj | state | gender |
+------+---------------+---------------+-----------+------------+------------+-------+--------+
| 105 | Shubham | Asst. Clerk | 61000.00 | 12345678 | 2026-04-01 | MH | F |
| 106 | Atulk | Clerk | 80000.00 | 123456780 | 2019-07-19 | Goa | M |
| 107 | Anand | Clerk | 90000.00 | 1234567890 | 2022-09-30 | Bihar | M |
| 111 | Chinmayee | MTS | 100000.00 | 9234567890 | 2022-10-30 | GJ | F |
| 201 | Devesh Sharma | Co-Manager | 75000.00 | 2345 | 2020-12-23 | UP | M |
+------+---------------+---------------+-----------+------------+------------+-------+--------+
5 rows in set (0.01 sec)NOT BETWEEN flips it: everything outside 20000–50000, both ends excluded this time. Together, the previous section's 3 rows and this one's 5 rows add up to all 8 rows in employee, BETWEEN and NOT BETWEEN on the same range always partition the table into two non-overlapping halves.
Matching either condition with OR
select * from employee where state='up' or state='uttarakhand';+------+---------------+---------------+----------+--------+------------+-------------+--------+
| code | name | desig | salary | mob | doj | state | gender |
+------+---------------+---------------+----------+--------+------------+-------------+--------+
| 101 | Om Sharma | Manager | 50000.00 | 12345 | 2020-12-23 | Uttarakhand | M |
| 201 | Devesh Sharma | Co-Manager | 75000.00 | 2345 | 2020-12-23 | UP | M |
| 301 | Arnav | Asst. Manager | 44000.00 | 123456 | 2020-12-23 | UP | M |
+------+---------------+---------------+----------+--------+------------+-------------+--------+
3 rows in set (0.00 sec)Unlike AND, which needed every condition satisfied, OR only needs at least one, matches 'UP', matches 'Uttarakhand', or, in principle, could match both, any single hit is enough to include the row.
The same OR chain, with IN
select * from employee where state in ('up','uttarakhand');+------+---------------+---------------+----------+--------+------------+-------------+--------+
| code | name | desig | salary | mob | doj | state | gender |
+------+---------------+---------------+----------+--------+------------+-------------+--------+
| 101 | Om Sharma | Manager | 50000.00 | 12345 | 2020-12-23 | Uttarakhand | M |
| 201 | Devesh Sharma | Co-Manager | 75000.00 | 2345 | 2020-12-23 | UP | M |
| 301 | Arnav | Asst. Manager | 44000.00 | 123456 | 2020-12-23 | UP | M |
+------+---------------+---------------+----------+--------+------------+-------------+--------+
3 rows in set (0.00 sec)Exactly the same result as the OR chain above, IN (...) is shorthand for "equals any of these values", state = 'up' OR state = 'uttarakhand' collapses into state IN ('up', 'uttarakhand'). Only worth reaching for OR by hand once there are just a couple of options; once the list of possible values grows, IN reads a lot cleaner than a long chain of ORs on the same column.
Inverting it with NOT IN
select * from employee where state not in ('up','uttarakhand');+------+-----------+---------------+-----------+------------+------------+-------+--------+
| code | name | desig | salary | mob | doj | state | gender |
+------+-----------+---------------+-----------+------------+------------+-------+--------+
| 104 | Devansh | Clerk | 31000.00 | 1234567 | 2023-04-12 | TN | F |
| 105 | Shubham | Asst. Clerk | 61000.00 | 12345678 | 2026-04-01 | MH | F |
| 106 | Atulk | Clerk | 80000.00 | 123456780 | 2019-07-19 | Goa | M |
| 107 | Anand | Clerk | 90000.00 | 1234567890 | 2022-09-30 | Bihar | M |
| 111 | Chinmayee | MTS | 100000.00 | 9234567890 | 2022-10-30 | GJ | F |
+------+-----------+---------------+-----------+------------+------------+-------+--------+
5 rows in set (0.00 sec)NOT IN flips it, every row whose state matches none of the listed values. Same relationship as BETWEEN/NOT BETWEEN earlier: this section's 5 rows plus the IN section's 3 rows add up to all 8 rows in employee, IN and NOT IN on the same list always partition the table.
Removing duplicates with DISTINCT
select desig from employee;+---------------+
| desig |
+---------------+
| Manager |
| Clerk |
| Asst. Clerk |
| Clerk |
| Clerk |
| MTS |
| Co-Manager |
| Asst. Manager |
+---------------+
8 rows in set (0.00 sec)select distinct(desig) from employee;+---------------+
| desig |
+---------------+
| Manager |
| Clerk |
| Asst. Clerk |
| MTS |
| Co-Manager |
| Asst. Manager |
+---------------+
6 rows in set (0.00 sec)Plain SELECT desig returns one row per employee, 'Clerk' shows up 3 times since 3 employees hold that title. DISTINCT collapses repeated values down to one row each, 8 rows becomes 6, the same 6 unique designations employee actually contains.
Sorting results with ORDER BY
select * from employee order by salary;Ascending is the default, no keyword needed, lowest salary first (104 at 31000.00) up to highest last (111 at 100000.00). This is a different order from every result so far, which sorted by code (the primary key) regardless of what was selected, ORDER BY overrides that and sorts by whatever column is named instead.
select * from employee order by salary desc;DESC reverses it, highest first. Same 8 rows, opposite direction, 111 now leads and 104 falls to the end.
select * from employee order by name;ORDER BY isn't limited to numbers, sorting name arranges rows alphabetically, 'Anand' first, 'Shubham' last. Ascending is still the default here too, ASC is legal to write but does nothing that isn't already happening.
select * from employee order by doj;Sorting doj (DATE) puts the earliest date first, 106 from 2019-07-19 leads, 105 from 2026-04-01 is last, genuinely chronological, not just comparing the text left to right. Worth also noticing 101, 201, and 301 all share the exact same doj, 2020-12-23, and MySQL leaves tied rows in whatever order it finds them in, no guaranteed tiebreaker unless a second column is added to the ORDER BY.
Pattern matching with LIKE
select * from employee where name like 'A%';+------+-------+---------------+----------+------------+------------+-------+--------+
| code | name | desig | salary | mob | doj | state | gender |
+------+-------+---------------+----------+------------+------------+-------+--------+
| 106 | Atulk | Clerk | 80000.00 | 123456780 | 2019-07-19 | Goa | M |
| 107 | Anand | Clerk | 90000.00 | 1234567890 | 2022-09-30 | Bihar | M |
| 301 | Arnav | Asst. Manager | 44000.00 | 123456 | 2020-12-23 | UP | M |
+------+-------+---------------+----------+------------+------------+-------+--------+
3 rows in set (0.00 sec)LIKE matches text against a pattern rather than an exact value. % stands in for "any sequence of characters, including none", so 'A%' means "starts with A, then anything after it", Atulk, Anand, Arnav, every name beginning with A matches, regardless of what follows or how long it is.
LIKE's other wildcard, _
select * from employee where name like "_____";_ matches exactly one character, no more, no less. Five underscores means "exactly 5 characters, anything in each spot", Atulk, Anand, Arnav, all 5 letters long, is what matches here, same 3 rows as the % example above by coincidence, but for a completely different reason, length instead of a starting letter. A name with 4 or 6 characters wouldn't match this pattern even if it also started with A.
LIKE works on dates too
select * from employee where doj like '%2026%';doj is a DATE column, not text, but LIKE still works on it, MySQL implicitly converts the date to its YYYY-MM-DD string form before matching. '%2026%' finds 2026 anywhere in that string, which in practice means "matches any date in the year 2026". Only 105 (2026-04-01) qualifies here.
LIKE with no wildcard at all
select * from employee where desig like 'Clerk';No %, no _, just a plain string. Without a wildcard anywhere in the pattern, LIKE behaves exactly like =, it only matches an exact value, 'Clerk' matches 'Clerk' and nothing else, 'Asst. Clerk' doesn't qualify even though it contains the word Clerk. The wildcards are what give LIKE its pattern-matching behavior, without one it's just a slower way to write =.
Wrapping % on both sides to search "contains"
select * from employee where desig like '%Clerk%';This is the fix for exactly what the previous section couldn't do. % on both sides means "anything before, anything after", so the pattern is really just "contains Clerk somewhere". Now 'Asst. Clerk' (105) matches too, alongside the three plain 'Clerk' rows, '%text%' is the standard shape for a "contains" search in SQL.
Combining _ and % in one pattern
select * from employee where desig like '_a%';The two wildcards aren't an either-or choice, they compose in the same pattern. _a% means "any 1 character, then a, then anything after that", so the match has to have a as its second character specifically. 'Manager' fits, M then a, then anything, only 101 qualifies here. 'Asst. Manager' doesn't match even though it also contains a right after its first letter, an _ counts space and punctuation as a character too, so its second character is s, not a.
Filtering for NULL with IS NULL / IS NOT NULL
select * from employee where mob is null;+------+-------+-------+----------+------+------+-------+--------+
| code | name | desig | salary | mob | doj | state | gender |
+------+-------+-------+----------+------+------+-------+--------+
| 113 | Vansh | Peon | 18000.00 | NULL | NULL | NULL | M |
+------+-------+-------+----------+------+------+-------+--------+
1 row in set (0.00 sec)select * from employee where mob is not null;+------+---------------+---------------+-----------+------------+------------+-------------+--------+
| code | name | desig | salary | mob | doj | state | gender |
+------+---------------+---------------+-----------+------------+------------+-------------+--------+
| 101 | Om Sharma | Manager | 50000.00 | 12345 | 2020-12-23 | Uttarakhand | M |
| 104 | Devansh | Clerk | 31000.00 | 1234567 | 2023-04-12 | TN | F |
| 105 | Shubham | Asst. Clerk | 61000.00 | 12345678 | 2026-04-01 | MH | F |
| 106 | Atulk | Clerk | 80000.00 | 123456780 | 2019-07-19 | Goa | M |
| 107 | Anand | Clerk | 90000.00 | 1234567890 | 2022-09-30 | Bihar | M |
| 111 | Chinmayee | MTS | 100000.00 | 9234567890 | 2022-10-30 | GJ | F |
| 112 | Anjali | Peon | 122345.00 | 22000 | NULL | UP | F |
| 201 | Devesh Sharma | Co-Manager | 75000.00 | 2345 | 2020-12-23 | UP | M |
| 301 | Arnav | Asst. Manager | 44000.00 | 123456 | 2020-12-23 | UP | M |
+------+---------------+---------------+-----------+------------+------------+-------------+--------+
9 rows in set (0.00 sec)NULL means "no value", and it genuinely can't be tested with = or !=, WHERE mob = NULL doesn't error, but it also never matches anything, NULL isn't equal to NULL, or to anything else, by SQL's own rules. IS NULL and IS NOT NULL exist specifically to test for this, they're the only correct way to filter on whether a value is missing.
WHERE mob IS NULL finds exactly the rows with nothing in mob, 113 here. WHERE mob IS NOT NULL finds every other row, everyone who actually has a mobile number on file.