SQL Introduction and Tutorial 2

[jroyce1]

Also in Oracle SQL you can do a similar table creation like this:

CREATE TABLE newTable AS
SELECT column_list
FROM table;



Personal blog: The Code Blog

 

[Golom]

For example, if you want all people who live in Alaska with blue eyes or people who live in California, you would use the following:

SELECT *
FROM address_list
WHERE (eye_color = 'blue' OR eye_color = 'hazel') AND state = 'CA'


If you don't use the parentheses your query will return all people in California or anyone with blue eyes or anyone in Alaska.

You have changed the SQL but not the descriptive text. You're still talking about Alaska but it no longer appears in the SQL.

 

[jroyce1]

Golom,
Thanks. Do you have a link to a good description and usage examples of 'OR' with parentheses? This is a bit of a confusing one and I want to make sure this is very clear in my SQL post.

Personal blog: The Code Blog

 

[r937]

> Do you have a link to a good description and usage examples of 'OR' with parentheses

i would use the eye color and state example

eye color hazel, eye color blue, state AZ, state CA

all you have to do is write a couple of SQL statements with ANDs and ORs and then interpret them correctly in the explanation

simple, eh

r937.com | rudy.ca

 

[PHV]

Bottom line: when you mix OR and AND operators, always use parentheses ! Don't rely on optimizer choices ...

 

[Golom]

... Do you have a link to a good description and usage examples of 'OR' with parentheses

I do as PHV recommends. If AND and OR both occur in a WHERE clause then I use parentheses ... even if the result would be the same without them.

I don't have a link but the rules are fairly simple. AND is processed first ... then OR is processed. So if c1, c2, c3, ... are logical conditions then

Where c1 OR c2 AND c3 OR c4 OR c5 AND c6

will be processed as

Where c1 OR [red][b]([/b][/red]c2 AND c3[red][b])[/b][/red] OR c4 OR [red][b]([/b][/red]c5 AND c6[red][b])[/b][/red]

resulting in an intermediate expression

Where c1 OR [red][b]c23[/b][/red] OR c4 OR [red][b]c56[/b][/red]

where c23 and c56 are the results of evaluation of the expressions in parentheses. That intermediate expression is then evaluated using OR rules which is the only remaining operator.

 

[jroyce1]

Thanks and that is helpful.

Personal blog: The Code Blog