How do you use logical operators in Google Sheets?

 All you need is to use the category 'Function->Logical' in the drop-down of the Insert Menu item shown here:

   

You can choose the operator you want such as 'AND'

AND operator takes two arguments and provides the result. Read the explanation that shows up as a drop-down.

Exaample 1: I have three numbers in three cells as shown

In the above you can see B1 is > than B2 and the answer is true.

Example 2: You need not give cell references to evlauate the result of an AND operation as shown below. Obviously, the result of 1>100 is 'false'

The following from the link 'Learn more' above.

AND function

The AND function returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.

Sample Usage

AND(A2 = "foo", A3 = "bar")

AND(TRUE,FALSE,TRUE)

AND(A1:A10,B1:B10)

AND(0,1,2,3)

Syntax

AND(logical_expression1, [logical_expression2, ...])

• logical_expression1 - An expression or reference to a cell containing an expression that represents some logical value, i.e. TRUE or FALSE, or an expression that can be coerced to a logical value.

• logical_expression2, ... - [ OPTIONAL ] - Additional expressions or references to cells containing expressions representing some logical values, i.e. TRUE or FALSE, or expressions that can be coerced to logical values.

Notes

• The number 0 is logically false; all other numbers (including negative numbers) are logically true.

See Also

OR: The OR function returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false.

NOT: Returns the opposite of a logical value - `NOT(TRUE)` returns `FALSE`; `NOT(FALSE)` returns `TRUE`.

Examples

Returns TRUE if all arguments are TRUE, returns FALSE if any element is FALSE, accepts both logical_value and range parameter.

How do you explain this correlated subquery?

The syntax of subqueries is as shown here.

Outer query(some operator) (Inner query)

While non-correlated subqueries are quite easy to understand correlated sub queries
are not quite obvious.

In the case of non-correlated subqueries, the inner query is evaluated first. The value or values returned from inner queries are used in the outer queries based on some operation.

In the case of correlated subquery the outer query is run and for each value returned by the outer query the inner query is evaluated and when the match is found the outer query stops. Usually the inner query just returns either some value or no value and the logical operation is usually Boolean (exists, not exists, any, all, etc.).

The correlated subquery you are asking about is the following posed to the Northwind database:

Use Northwind
Go
Select o.EmployeeID, ShipName, o.OrderID ​
from Orders o​
Where  exists​
(Select i.ShipCity​
from Orders i​
Where i.ShipCity=o.ShipCity and i.EmployeeID in (5,7))
Go

This returns the following result set (here are sme 6 rows out of 674 rows):

CorrelatedSubquery
You are probably intrigued why you are getting employee ID's other than 5 and 7 in
the result.

The reason for this is the inner query result when processed with the logic (exists) returns true or false. If it is true the Orders table returns the columns requested. Inner query result set are not part of the rows returned.

Try the same query replacing 'exists' with 'not exists'. You will find that the subquery returns 156 rows.

The total of all rows returned by the query,

Select o.EmployeeID, o.ShipName, o.OrderID ​
from Orders o​

is 830 which is (156+674).