EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials MySQL Tutorial Condition in MySQL

Condition in MySQL

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 10, 2023

Condition in MySQL

Introduction to Condition in MySQL

Condition in MySQL is an open-source RDBMS (Relational Database Management System) that uses a standard language SQL – Structured Query Language for manipulating, storing, and retrieving database records. In simple words, we can say that MYSQL is a Database server that is a fast, secure, and easy-to-use application for many small and big businesses and different purposes – Web Database, Data Warehousing, E-Commerce, Logging applications, and more.

ADVERTISEMENT
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

A database encompasses one or multiple tables with rows and columns. A name represents each table, and SQL statements perform any action on a database. Here, we will discuss Conditional statements in MYSQL, which are based on some conditional expressions. An expression can be any arrangement of MySQL literals like variables, operators, and functions that, on execution, returns a logical value if the condition is satisfied.

Conditional Operators in MySQL

Let’s first view some of the important SQL statements with the syntax:

1. SELECT– extracts/selects records from a database. ‘*’ denotes ‘all’.

SELECT column1, column2,......  FROM table_name;
SELECT * FROM table_name;

2. UPDATE– updates existing fields in a database

UPDATE tablename SET col1 = value1, col2 = value2, ... WHERE condition;

3. DELETE– deletes records from a database

DELETE FROM table_name WHERE condition;

4. DROP DATABASE – Deletes database

DROP DATABASE database_name;

5. INSERT INTO– adds new records into the database

INSERT INTO name_of_table (col1,col2,   ...) VALUES (val1, val2, ...);

6. CREATE DATABASE– generates a new database

CREATE DATABASE databasename;

7. CREATE TABLE– generates a new table

CREATE TABLE tablename ( col1 datatype, col2 datatype, col3 datatype, .... );

8. ALTER TABLE– make changes to a table

ALTER TABLE table_name ADD column_name datatype;

9. DROP TABLE– removes a table

DROP TABLE table_name;

10. CREATE INDEX– produces an index (search key)

CREATE INDEX index_name ON table_name (column1, column2, ...);

11. DROP INDEX– removes an index

DROP INDEX index_name ON table_name;
Note: SQL keywords are case-insensitive. For example, select can also be written as SELECT. The semicolon is used to distinguish each SQL statement in database systems.

Types of SQL Operators

The different types of SQL Operators are given below:

1. SQL Arithmetic Operators

Operators Descriptions
+                                   Add
–                                   Subtract
*                               Multiply
/                                  Divide
%                                   Modulo

2. SQL Bitwise Operators

Operator Description
& Bitwise AND
| Bitwise OR
^ Bitwise exclusive OR

3. SQL Comparison Operators

Operator Description
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to

4. SQL Logical Operators

Operator Description
ALL TRUE if all of the subquery values satisfy the condition.
AND  TRUE if all the conditional expressions separated by AND are TRUE.
ANY TRUE if any of the subquery values satisfy the condition.
BETWEEN TRUE if the operand is inside the series of comparisons.
EXISTS TRUE if the subquery results in one or more fields.
IN TRUE if the operand matches one of a list of expressions.
LIKE TRUE if the operand equals a pattern.
NOT Shows a record if the condition(s) is FALSE.
OR TRUE if any of the conditions separated by OR is valid.
SOME  TRUE if any of the subquery values satisfy the condition.

5. Conditional Operators

You can learn and practice SQL queries from educba.com in a straightforward understanding way.

1. IF() Function

If the condition is TRUE result is “YES,” and if the condition is FALSE, then “NO”.

Syntax:

IF(condition, value_if_true, value_if_false)

SQL Statement: 

SELECT IF(400<2000, "YES", "NO");

Output:

condition in mysql

2. COALESCE () Function

Results in the first non-null value in a list.

Syntax:

COALESCE (val1, val2, ...., val_n)

SQL Statement:

SELECT COALESCE (NULL, 1, 5, 'example.com');

Output:

condition in mysql

3. CASE Function

Checks all the conditions, and if the first condition is met, returns a value and will not read further. If no conditions are fulfilled, then the value will be returned in the ELSE clause. But again, it will return NULL if no ELSE portion and no conditional expressions are true.

Syntax:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

Suppose a table Customer:

condition in mysql

SQL Statement:

SELECT CustomerID, CustomerName, Address FROM Customers
ORDER BY (CASE
WHEN Address IS NULL THEN  City
ELSE Address
END);

Output:

condition in mysql

4. ISNULL () Function

Results 1 if the expression is NULL; otherwise, returns 0.

Syntax:

SELECT ISNULL (expr)

SQL Statement:

SELECT ISNULL (NULL);

Output:

condition in mysql

SQL Statement:

SELECT ISNULL ("Hello!");

Output:

condition in mysql

5. NULLIF () Function

If two expressions are equal on the comparison, then it returns NULL. Otherwise, if not, then the first expression is the result.

Syntax:

SELECT NULLIF (expr1, expr2)

SQL Statement:

SELECT NULLIF (30, 30);

Output: 

condition in mysql

SQL Statement:

SELECT NULLIF (50, "Test");

Output:

condition in mysql

6. IFNULL () Function

A particular value is returned if the expression is NULL; if NOT NULL, it returns the expression. Syntax:

SELECT IFNULL (expr, alt_value)

SQL Statement:

SELECT IFNULL (NULL, "Learning SQL");

Output: 

condition in mysql

SQL Statement:

SELECT IFNULL ("100", "Database SQL");

Output: 

Database SQL

7. GREATEST

It results in the biggest value from a list of two or multiple terms. If any conditional expression in the list is NULL, it returns NULL.

Syntax:

SELECT GREATEST (expr1, expr2 [...])

SQL Statement:   

SELECT GREATEST(10, 35, 88, 55);

Output:

Greatest

8. IN

Check whether a value is inside a set of given or listed values. We can use it with CHECK, WHERE, and creation of views.

Syntax:

WHERE column IN (y1, y2, y3 [...])

SQL Statement:

SELECT * FROM Customers WHERE city IN ('Bareilly', 'Katihar',’Delhi’);

Output:

Customized ID

9. LEAST

It provides the least value from the list of two or multiple expressions; the result will be NULL if there is a NULL value expression in the list.

Syntax:

SELECT LEAST (expr1, expr2 [, ...])

SQL Statement:     

SELECT LEAST (10, 35, 88, 55); Output:

Least

Conclusion – Condition in MySQL

If we want to create a website that displays data from a database, we need to know the following:

  • An RDBMS database platform such as MS Access, SQL Server, MySQL.
  • PHP, ASP, Python, Java, Node.js, JavaScript-like server-side scripting languages.
  • Query language SQL to access the records from the database we want to show on the webpage.
  • Knowledge of HTML and CSS to elegance the page.

Generally, IFandCASE is the standard conditional statement used in MYSQL. These conditional operators have reduced using multiple OR conditions for SELECT, UPDATE, INSERT, or DELETE SQL statements. So, conditional operators in MYSQL are probably helpful for filtering the data and providing exact results based on certain conditions so that it saves our time and effort in fetching information from the database.

Recommended Articles

We hope that this EDUCBA information on “Condition in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Cursor in MySQL
  2. Cursor in MySQL | Examples
  3. Complete Guide to Conditional Operators in C#
  4. Guide to SQL Arithmetic Operators
ADVERTISEMENT
C++ PROGRAMMING Course Bundle - 9 Courses in 1 | 5 Mock Tests
40+ Hour of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
ASP.NET Course Bundle - 28 Courses in 1 | 5 Mock Tests
123+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
205+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SOFTWARE TESTING Course Bundle - 13 Courses in 1
53+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW