SQL AND and OR operators behave unexpected-CodePudding

I'm making the below query:

SELECT
    `user_organisation`.*,
    `users`.`full_name`,
    `users`.`email`,
    `users`.`avatar`
FROM
    `user_organisation`
INNER JOIN `users` ON `users`.`id` = `user_organisation`.`user_id`
WHERE
    `users`.`full_name` LIKE '%test%' OR `users`.`email` LIKE '%test%' AND `user_organisation`.`organisation_id` = 111

And I get the results containing user_organisation.organisation_id equals 111 and 222. I want to search records with users.full_name or users.email containing value test. This SQL successfully searches users with full name or email containing string test but not only user_organisation.organisation_id=111.

What the reason for that unexpected behaviour? I sure I'm missing something but I can't see...

CodePudding user response：

You need parentheses in your WHERE clause around the two ORed terms:

SELECT uo.*, u.full_name, u.email, u.avatar
FROM user_organisation uo
INNER JOIN users u ON u.id = uo.user_id
WHERE (u.full_name LIKE '%test%' OR u.email LIKE '%test%') AND
      uo.organisation_id = 111;

Note also that in my answer I am using table aliases (which are abbreviations for full table names). Also, I removed the unnecessary backticks.