473,700 Members | 2,774 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Howto use joins instead of subqueries?

TH
Hi,

Trying to get a grip on the "join" thing :)
Up until now, I allways used this kinda method:

"(select t1.a from t1 where t1.b in (select t2.ab from t2 where t2.b=0))"

How can this be accomplished using joins? And if you have the time please
explain the "bits" :)

Thank you.

--
TH
Jul 20 '05 #1
3 8107
select t1.a from t1 where t1.b in (select t2.ab from t2 where t2.b=0

Using joins it would be something like this

select t1.a from t1 join t2 on t1.b = t2.ab
where t2.b = 0

This would assume an inner join so all that would be returned would be
t1.a where there was a match between t1.b and t2.ab.

Example: If I created two simple tables:

create table table1 (ikey int)
create table table2 (ikey int)

and populated them

insert into table1 select '55' union all select '56' union all select
'57'
insert into table2 select '55' union all select '56' union all select
'57' union all select '58'

Now I can write some join statements:

select a.ikey from table1 a join table2 b on a.ikey = b.ikey

notice I am able to use an alias for the table by using it in the from
statement (NOTE: if you use an alias you have to be consistent, you can
not refer back to the table name elsewhere in the query)

You will note that the above query only returned 3 rows, not the fourth
row we inserted into table2 becuase there was no match in table1. If you
wanted to see all values in table2 you could write a right join:

select a.ikey as Tbl1_Key, b.ikey as Tbl2_Key
from table1 a right join table2 b on a.ikey = b.ikey

This will return all matching rows and any rows in table 2 (because it
is on your right in the from clause) You will see a null value for the
'58' for table 2 as there is no match in table 1.

To get more thorough explanation of joins, check out BOL (books-online)
documentation that comes with SQL Server.

HTH
Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #2
TH
Very nice description.
Thanks!

--
TH
Pffmppppmppffpm mpp fmpmfpmpp mpmppfmfm
mmpmppmpfppfpff mpp pffmpppfmpmfffm mffpppmfm...
<http://www.namesuppres sed.com/kenny/>
Jul 20 '05 #3
JOIN has better performance than sub-query.

For your instance, you can get even better performance than JOIN if you
query like this:

select t1.a from t1
where exists ( select t2.ab from t2 where t2.b=0 and t2.ab = t1.b )

if t1 and t2 are very large tables, you will benifit a lot. Otherwise,
forget it, no difference.

George S. Z

"TH" <th_at_cogito.g reatdanedk> wrote in message
news:3f******** *************** @dtext02.news.t ele.dk...
Very nice description.
Thanks!

--
TH
Pffmppppmppffpm mpp fmpmfpmpp mpmppfmfm
mmpmppmpfppfpff mpp pffmpppfmpmfffm mffpppmfm...
<http://www.namesuppres sed.com/kenny/>

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1365
by: Patrick Crowley | last post by:
I'm creating a tool to browse a database of movie listings. The browser pulls up 25 results at a time, and you can page through them using 'Next' and 'Prev' tools. Pretty basic stuff. Here are my tables: movies directors comments movies_directors movies_comments
0
1543
by: Vijay | last post by:
Hello friends, I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDate othertableid value -------- ------------- ------------ ----- 1 2004-01-10 1 10
2
1810
by: Joel | last post by:
Hi, Table Oelsls contains all products bought (history) table Invt contains the products to sell (Inventory) I'm trying with no success to list all products in Oelsls table (field name in both tables: part_id) that are not in the Invt table. My objective is a grid of all Special (non inventory) products. Can anyone produce the correct sql statement?
6
2343
by: J Belly | last post by:
Hi, all: This is probably a simple problem, but, as an SQL newbie, I'm having a little trouble understanding multi-joins and subqueries. I have the following tables and columns: MemberTable -----
4
1424
by: Scott Marquardt | last post by:
My SQL acumen stems from just a couple courses, and everything since from the trenches. Fun + angst over time. I'm needing some advice on joins. Though I understand the basics, I'm having problems abstracting from instances where it's easy to think about discrete key values (. . . and studentid = 1234) to entire sets of users, with the joins doing their work. For example, currently I'm going nuts trying to return dates for which...
0
1762
by: Joerg Ammann | last post by:
hi, os: aix 4.3.3 DB2: version 7 FP3 we are using a federated DB setup, datasource and fed-Db are both V7FP3 (in fact they are on the same server) and are having massiv performance problems. i tracked it back to the way the queries are push-downed to the
1
2877
by: lizandra | last post by:
Greetings, I am a newbie, I have been working to extract data from a basic sales db and trying to decide when I should use joins and when I should use subqueries. Much of what I read online says use subqueries only as a last resort, especially correlated ones as they do a record by record data evaluation and are very resource intensive. Are joins and WHERE and HAVING clauses the preferred method for extracting related data? But in SQL...
5
4072
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the framework to auto-generate a table adapter specifying both stored procs as Get/Fill and Update. The problem is that columns from the JOINed table seem to marked as 'read-only' so trying to update a row results in an exception. BTW, by default a...
1
2578
by: jghouse | last post by:
Everyone, I am just starting my work with subqueries and I am a little lost on how to handle the syntax on one part. I have a query that will contain some subqueries for the purpose of counting the records in three different tables. I understand how to create the subqueries for the most part but I am unsure how to set-up the join since all 3 subqueries will need to join back to the same table that also has to be joined to one other...
0
8718
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
8642
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9206
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8916
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7802
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6558
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4400
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2383
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2022
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.