Скачать или смотреть How to Effectively Search Your Table in MySQL: Fixing the LIKE Query Issue

Discover the solution to your SQL query problems with our guide on searching tables using `LIKE`. We'll help you filter out deleted records effectively!
---
This video is based on the question https://stackoverflow.com/q/72653761/ asked by the user 'mikk' ( https://stackoverflow.com/u/17237390/ ) and on the answer https://stackoverflow.com/a/72653788/ provided by the user 'blabla_bingo' ( https://stackoverflow.com/u/18554369/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: how to search table where like '%data%' or like '%data%' and deleted = 0?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Effectively Search Your Table in MySQL

Dealing with databases can sometimes feel like navigating a maze, especially when it comes to constructing query statements to return results you need. One common challenge many developers face relates to searching records while ensuring that specific conditions are met. In this guide, we will tackle a common query-related problem: how to properly filter results using the LIKE clause while excluding deleted entries.

The Problem

Consider the following SQL statement:

[[See Video to Reveal this Text or Code Snippet]]

While the intention is clear – retrieving data where either the nomor_formulir contains "mikha" or the harga_pelita contains "5," and also filtering for non-deleted records (deleted=0) – the results are not as expected. Records with a status of deleted=1 still appear.

Why This Happens

The issue arises from the order of operations in SQL. The AND operator has a higher precedence than OR, which leads to unintended consequences. Due to the way the conditions are structured, the query could be interpreted as:

Condition 1: nomor_formulir LIKE '%mikha%'

Condition 2: harga_pelita LIKE '%5%' and deleted=0

This means that if nomor_formulir contains "mikha," it will show results regardless of the deleted status.

The Solution

To fix this issue, we can introduce parentheses to explicitly define the order of operations. This will ensure that the AND condition applies correctly to the combination of nomor_formulir and harga_pelita.

Here’s the corrected version of the query:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query

Using Parentheses: The expression (nomor_formulir LIKE '%mikha%' OR harga_pelita LIKE '%5%') groups the two search conditions together. This means your query will first check for either field matching its criteria before applying the AND deleted=0 condition.

Ensuring Non-Deleted Records: The AND deleted=0 will only filter results after confirming the initial LIKE conditions, ensuring that any rows returned will not be marked as deleted.

Key Takeaways

Order of Operations: Remember that AND takes precedence over OR. Use parentheses to clarify intended logic in complex queries.

Test Your Queries: Always test SQL queries to ensure they return the expected results, especially when dealing with LIKE conditions and status flags.

Readability Matters: Well-structured queries are easier to read and maintain. Don’t hesitate to use parentheses for clarity.

By applying these practices, you can efficiently navigate through your SQL queries, optimizing your results while ensuring integrity in the returned data sets. Happy querying!