where vs inner join

Select name from city where CITY.COUNTRYCODE = COUNTRY.CODE;


Select NAME from CITY inner join CITY.COUNTRYCODE = COUNTRY.CODE;


in above queries what is difference between where and inner join

Are these queries are case sensitive like below


Select name from city where CITY.CountryCode = COUNTRY.Code;



Select name from city where CITY.COUNTRYCODE = COUNTRY.CODE;



below query how is different

SELECT City.Name
FROM City, Country
WHERE City.CountryCode = Country.Code AND Continent = 'Africa' ;

in the FROM clause do i need to mention both tables like city , country or one is ok then where


can i write above using inner clause without where?
any performance hits or advantages, disadvantage in either approaches?
LVL 7
gudii9Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>Select NAME from CITY inner join CITY.COUNTRYCODE = COUNTRY.CODE;

This should generate an error.

>>Are these queries are case sensitive like below

In Oracle:  No.  I cannot speak to other database products.

>>below query how is different

It shouldn't be.




You can probably move MOST of your WHERE clause to the JOIN line itself.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
in above queries what is difference between where and inner join
Assuming that you forgot to add the COUNTRY table, there are no differences in terms of query execution. It's only a visualization difference as the INNER JOIN should be used to identify the relationship between the tables (but this is not really a requirement for query executions).

Are these queries are case sensitive like below
It depends on the collation of the databases. Databases that are configured to use case sensitive collations, needs to have the object names written in the correct case. Otherwise it doesn't matter.

in the FROM clause do i need to mention both tables like city , country or one is ok then where
FROM is only to specify table names and the type of JOINs.

can i write above using inner clause without where?
Yes.

any performance hits or advantages, disadvantage in either approaches?
No. As I mentioned before, it's only for visualization. I prefer to use the JOIN clause to identify the relationship between the tables but there's no impact of any kind.
0
pcelbaCommented:
SELECT City.Name
FROM City, Country
WHERE City.CountryCode = Country.Code AND Continent = 'Africa' ;

vs.

SELECT City.Name
FROM City
 INNER JOIN Country ON City.CountryCode = Country.Code
WHERE Continent = 'Africa' ;

Both ways are equivalent from the engine point of view.  JOIN is the recommended one.
You should qualify all columns and use schema in conjunction with table names so better is to use:

SELECT City.Name
FROM dbo.City
 INNER JOIN dbo.Country ON City.CountryCode = Country.Code
WHERE Country.Continent = 'Africa' ;
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

pcelbaCommented:
Case sensitivity depends on your Database settings. Used collation controls this behavior. More obvious is to ignore the difference between upper/lower case.
0
pcelbaCommented:
Of curse, you can also write:

SELECT City.Name
FROM dbo.City
 INNER JOIN dbo.Country ON City.CountryCode = Country.Code AND Country.Continent = 'Africa' ;

with no difference comparing to the condition in WHERE (in this simple case).

This is not valid if you would use LEFT OUTER JOIN.
0
gudii9Author Commented:
Both ways are equivalent from the engine point of view.  JOIN is the recommended one.
why JOIN is recommended
This is not valid if you would use LEFT OUTER JOIN.
why not valid for left outer join.

is there something called left inner join? no right?
0
slightwv (䄆 Netminder) Commented:
When two tables are involved you ALWAYS JOIN.  The only difference is if you use the new ANSI join syntax or the old way of using both tables in the FROM and the columns in the where clause.

Most database these days allow the ANSI syntax and many find it easier to read so everyone is slowly starting to use it.

>>is there something called left inner join? no right?

Use the VENN diagram link I posted in your other question.  It will tell you the available options and what each one does.
0
pcelbaCommented:
To use JOINs instead of more tables in the WHERE clause is more descriptive and better understandable syntax. Tables in WHERE are always joined with INNER JOIN whereas JOIN allows more variants.

More about JOINS is described e.g. here: https://www.w3schools.com/sql/

SELECT City.Name
FROM dbo.City
 INNER JOIN dbo.Country ON City.CountryCode = Country.Code
WHERE Country.Continent = 'Africa' ;

is exactly same as

SELECT City.Name
FROM dbo.City
 INNER JOIN dbo.Country ON City.CountryCode = Country.Code AND Country.Continent = 'Africa' ;

If you imagine a situation where some countries do not have continent assigned (Continent = NULL) then you would need to use OUTER JOIN:

SELECT City.Name, Country.Continent
FROM dbo.City
 LEFT OUTER JOIN dbo.Country ON City.CountryCode = Country.Code ;

Above select will list all cities with appropriate continent. If some country does not have continent assigned then you should see NULL in the Continent column in above select. You should also see NULL in cities which do not have Country assigned.

And now you may elaborate with additional filter:

SELECT City.Name
FROM dbo.City
 LEFT OUTER JOIN dbo.Country ON City.CountryCode = Country.Code
WHERE Country.Continent = 'Africa' ;

vs.

SELECT City.Name
FROM dbo.City
 LEFT OUTER JOIN dbo.Country ON City.CountryCode = Country.Code AND Country.Continent = 'Africa' ;

BTW, is your data model correct?  Some countries are on more than one continent. Examples are e.g. Russia, Turkey.
0
pcelbaCommented:
Hi gudii9,

did we explain everything about joins? Do you have additional questions about joins? If yes then please ask, if not then please award points and close the question.

TIA
Pavel
0
pcelbaCommented:
All answers were correct.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.