WHERE Clause in MariaDB

MariaDB uses SQL (Structured Query Language) and it is an open-source relational database management system (RDBMS) for managing and manipulating data. The WHERE clause in SQL queries is used to filter and obtain specific data. The ability to remove and retrieve specific data using the WHERE clause in SQL queries is one of the main features that makes MariaDB robust. This article will examine the syntax and many uses for the WHERE clause in MariaDB.

MariaDB's WHERE Clause

The WHERE clause, which defines a search criterion for picking rows, is an optional component in the select statement. To filter the results of a SELECT, INSERT, UPDATE, or DELETE query, we will use the WHERE clause in MariaDB.

Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition;

• SELECT Clause: It specifies the columns you want to retrieve from the table.
  
• FROM Clause: It specifies the table from which the data will be retrieved.
  
• WHERE condition: It filters the rows based on specific conditions and it is the statement that details the standards by which the data will be filtered.

Common Operators Used With WHERE Clause

• =
• <> or !=
• <
• >
• <=
• >=
• AND, OR, NOT

Let's create a table employees and insert data into this.

Query:

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
salary DECIMAL(10, 2)
);

Query:

INSERT INTO employees VALUES
(1, 'John', 'Doe', 3, 50000.00),
(2, 'Jane', 'Smith', 2, 60000.00),
(3, 'Bob', 'Johnson', 1, 75000.00),
(4, 'Alice', 'Williams', 2, 65000.00),
(5, 'Charlie', 'Brown', 1, 70000.00);

WHERE Clause With Single Condition

Query:

SELECT * FROM employees WHERE salary > 30000;

This query will retrieve all the rows in the employees table where salary is greater than 30000.

Output:

WHERE Clause With Comparison Operators

Query:

SELECT * FROM employees WHERE first_name = 'John';

This query will retrieve employess with first name john

Output:

Query:

SELECT * FROM employees WHERE salary <> 60000;

This query will retrieve employees with salary not equal to 6000

Output:

WHERE Clause With Logical Operators

Query:

SELECT * FROM employees WHERE salary > 50000 AND department_id = 1;

This query will give all the employees who have salary greater than 5000 and belongs to it department,

Output:

Query:

SELECT * FROM employees WHERE salary < 50000 OR department_id = 2;

This query will give all the employees who have salary less than 5000 or who belongs to sales department.

Output:

Query:

SELECT * FROM employees WHERE NOT department_id = 1;

This query will give all the employees who does not belongs to it department.

Output:

Conclusion

In MariaDB, the WHERE clause is an effective tool for selecting and obtaining certain data from tables. It is essential to comprehend its syntax and different operators when creating accurate and efficient SQL queries. Understanding the WHERE clause is crucial for effective database management in MariaDB, whether you are retrieving information, editing entries, or removing records.