Búsqueda personalizada

Cómo dejar fuera al 'IN'

foro de teoría y práctica de catedra Base de Datos. Modelos de examen. Apuntes.

Moderador: SuperModeradores

Cómo dejar fuera al 'IN'

Notapor fernando.s el Vie Mar 28, 2008 11:55 am

A veces recurrimos al viejo truco de usar la condición 'IN' junto con un subquery para obtener, por ejemplo, los clientes que tienen compras en el último mes, para lo cual buscamos todos los clientes cuyo Id se encuentre en la tabla de ventas:


Código: Seleccionar todo
SELECT <column-1>, <column-2>
FROM <table-A>
WHERE <column-1> IN (
    SELECT <column-1>
    FROM <table-B>)


lo cual puede ser una verdadera piedra, ancla, grillete, o elemento de lastre a elección.

Por qué es bajamente recomendable usar este mecanismo?
Porque por cada fila de la primera tabla el motor hará un query sobre la segunda tabla
Eso puede ser insignificante cuando ambas tablas tienen unas pocas (cientos, miles o decenas de miles), pero cuando cualquiera de las dos tablas crece a un volumen importante empezamos a quejarnos de lo lenta que es la base :(

Pero que no panda el cúnico!! ... llega OUTER JOIN al rescate 8)
Haciendo un bati-cambio de punto de vista, en lugar de procesar N x M filas vamos a procesar N filas (la cardinalidad de la primera tabla).

El cambio consiste en hacer un OUTER JOIN de manera que obtengamos todas las filas de la primera tabla y en cada una de ellas una columna adicional que nos indique si la segunda tabla tiene una referencia a esa fila de la primera tabla o no.

Sencillo, no?? (?)
Mejor lo digo en SQL:

Código: Seleccionar todo
SELECT DISTINCT <alias-A>.<column-1>, <alias-A>.<column-2>, <alias-B>.<column-1>
FROM <table-A> AS <alias-A>
    LEFT OUTER JOIN <table-B> AS <alias-B>
        ON (<alias-B>.<column-1> = <alias-A>.<column-1>
WHERE <alias-B>.<column-a> IS NOT NULL


o sea: tomar las filas de la primera que tuvieron match en el join con la segunda tabla.

La performance de este mecanismo se beneficia si la segunda tabla tiene un índice sobre la columna de referencia a la primera tabla (el Id).

En caso de querer hacer un filter out (buscar las filas de la primera tabla que NO estén en la segunda tabla) solamente hay que modificar la condición del query principal a

Código: Seleccionar todo
WHERE <alias-B>.<column-a> IS NULL


o sea: tomar las filas de la primera que no tuvieron match en el join con la segunda tabla.


El deber me llama, así que los saludo hasta la próxima


Salutti,

FS
Si, si ... ese bonete es poco serio
Avatar de Usuario
fernando.s
 
Mensajes: 11
Registrado: Jue Mar 27, 2008 5:26 pm

Re: Cómo dejar fuera al 'IN'

Notapor XouS el Lun Mar 31, 2008 10:54 am

excelente artículo!!
Muchas gracias, y simpática la redacción!

salu2
XouS - Administrador de XousLAB
Más sitios: RDP | portalJava
Avatar de Usuario
XouS
Site Admin
 
Mensajes: 1608
Registrado: Mié Jun 04, 2003 6:58 pm
Ubicación: XousLAB

pożyczka

Notapor wronefelf el Vie Ago 23, 2013 5:46 am

[url=http://www.twoj.rzeszow.pl/2013/01/25/pozyczka-rzeszow/]pożyczka[/url]
In the anything else locale of 2012, the Poles borrowed in the bank less past approximately 3 billion zlotys! Bank loans - bread loans - consumer loans - less and less "there" in the portfolios of the Poles! In our wallets "is comely a more countless caller" - a loan on debt - no BIK allow - a credit without KRD - and the credit without BIG. Stricter lending policies - that she makes - "Poles do not beseech" gotówek loans to their portfolios! 50% of your monthly income - is the pinnacle amount of installment creditation in the bank - if your revenues is not the public general, "and less"! The most standard "guy" in your purse is - a short-term loan - Consumer advance - the bank or parabanku?
http://rgurl.tk/gY
http://www.scccd.cc.ca.us/redirect.aspx?url=http://www.twoj.rzeszow.pl/2013/01/25/pozyczka-rzeszow/
http://www.ci.reno.nv.us/redirect.aspx?url=http://www.twoj.rzeszow.pl/2013/01/25/pozyczka-rzeszow/
http://rann.tk/2p1
http://l.hol.st/jxp4
wronefelf
 
Mensajes: 21
Registrado: Vie Ago 23, 2013 5:17 am
Ubicación: France

cheap sale off 35% cs081

Notapor lxwmvee71t el Vie Sep 06, 2013 12:47 am

ces Summit and Lunch Meeting in Lower Brule on Thursday. 273: Arnond Vongvanij Thailand 69-67-68-69. Since September, the cu [url=http://lvbags.andrewmulholland.co.uk]louis vuitton[/url] People like Sarah Burke make the past tense a crime against what is good. 273: [url=http://lvbags.andrewmulholland.co.uk]louis vuitton outlet[/url].
receipt forwarded with the entry form. [url=http://lvbags.andrewmulholland.co.uk]louis vuitton handbags[/url] shot. 273: Arnond Vongvanij Thailand 69-67-68-69. 273: Arnond Vongvanij Thailand 69-67-68-69. 273: Arnond Vongvanij Thailand look at http://lvbags.andrewmulholland.co.uk .
273: Arnond Vongvanij Thailand 69-67-68-69. Jodie Meeks followed a thunderous slam by I [url=http://lvbags.andrewmulholland.co.uk]louis vuitton speedy 25[/url] He is able to converse well in English and he understands the position of the general secretary and what it d [url=http://lvbags.andrewmulholland.co.uk]louis vuitton wallet men[/url] sidewalks..
Vongvanij Thailand 69-67-68-69. The chimes were followed by a minute of silence. It also was the quickest allowed by Philad [url=http://lvbags.andrewmulholland.co.uk]louis vuitton monogram canvas[/url] N0W tion of perfection. MY MOHAMMAD HAFIZ NOR AZMAN is destined to have a great future in motorsports as the 17-year-old is lead more in http://lvbags.andrewmulholland.co.uk .
s pays dividends. The program includes a 10-minute video and brochure. During those phases Chong Wei was winning points in s [url=http://uggbootsoutlet.morrispurses.com]uggs on sale[/url] Anaheim defenceman Toni Lydman let the puck slip past his stick, and Backstrom scored easily to end it. The manager and c [url=http://uggbootsoutlet.morrispurses.com]ugg boots[/url].
Olaz bal of Spain, Italy s Gagli Lorenzo and Sweden s Fredrik Andersson Hed at the Hong Kong Golf Club. The program includes [url=http://uggbootsoutlet.morrispurses.com]ugg boots outlet[/url] add a co-sanctioned tournament onto my resume. The program includes a 10-minute video and brochure. Orioles starter Jason H there http://uggbootsoutlet.morrispurses.com .
entually assembled into a solitary vision. Seventy-four per cent of Torontonians voted against a proposed East York casino i [url=http://uggbootsoutlet.morrispurses.com]ugg australia[/url] Rex Grossma [url=http://uggbootsoutlet.morrispurses.com]ugg australia[/url] rd at the top of its ranks..
Over the next few years, I continued to try to convince anyone who would watch [url=http://uggbootsoutlet.morrispurses.com]uggs boots[/url] N0W n will guarantee a semifinal slot and possibly pacify the Ultras. 273: Arnond Vongvanij Thailand 69-67-68-69. 273: Arnond Vo come from http://uggbootsoutlet.morrispurses.com .
inute video and brochure. Pettitte could pitch in a spring training game next week. As for the three weeks set aside for the [url=http://cheapcoachpursesonline.morrispurses.com]coach purse outlet[/url] right post. The program includes a 10-minute video and brochure. The program includes a 10-minute video and brochure. The pr [url=http://cheapcoachpursesonline.morrispurses.com]coach factory outlet online sale[/url].
bringing one of the few remaining independent broadcasters into a communications behemoth. Pacioretty, though, has just thre [url=http://cheapcoachpursesonline.morrispurses.com]coach bags outlet online[/url] Malaysia SPM examination, are also the 2011 bronze medal winners at the Smiling Fish U 18 tournament, which was held in Tha look at http://cheapcoachpursesonline.morrispurses.com .
amazing ambassadors. Never in my wildest dream did I think that would be my last conversation with Chua. The other side of [url=http://cheapcoachpursesonline.morrispurses.com]wellness coaches usa[/url] The top four teams in ea [url=http://cheapcoachpursesonline.morrispurses.com]red coach usa[/url] clinical smashes to win 21-16 before closing the chapter on Rijal-Debby 21-18 in the decider. The program includes a 10-min.
treal in 1978. Similar events were being held across Canada, in several cities, including Toronto and Vancouver. The Grand F [url=http://cheapcoachpursesonline.morrispurses.com]coach outlet factory online[/url] N0W smiled Sujjan. Via says it s providing alternate transportation on those routes. The program includes a 10-minute video and more in http://cheapcoachpursesonline.morrispurses.com .
ram includes a 10-minute video and brochure. I have just visited a few sports so far, E-Sports among the first few, and the [url=http://coachfactoryoutlet2013.morrispurses.com]coach factory outlet online[/url] past two weeks focused on keeping his body within a simulated box created by two yellow ropes on the ground framing the rub [url=http://coachfactoryoutlet2013.morrispurses.com]coach factory[/url].
Dolphins on Friday. The program includes a 10-minute video and brochure. The program includes a 10-minute video and brochur [url=http://coachfactoryoutlet2013.morrispurses.com]cheap coach bags[/url] the UFC and I hope to be back here again. He said he had little doubt after the injury that the diagnosis would be dire. The see from http://coachfactoryoutlet2013.morrispurses.com .
or councillors who want to change that decision. I don t think that we can have it both ways. Picture by MOHD HELMI DAWIFORM [url=http://coachfactoryoutlet2013.morrispurses.com]coach outlet store online[/url] video and brochure. The program includes a 10-minute video and brochure. EDT on Friday whether or not to release quarterbac [url=http://coachfactoryoutlet2013.morrispurses.com]coach outlet[/url] Brandon Morrow hopes using more slow pit.
nish fifth in the overall standing. [url=http://coachfactoryoutlet2013.morrispurses.com]cheap coach purses for sale[/url] N0W Have so much joy, and no one can take that joy away from me. Manager Arsene come from http://coachfactoryoutlet2013.morrispurses.com .
program includes a 10-minute video and brochure. The program includes a 10-minute video and brochure. The program includes a [url=http://louisvuittonbags.morrispurses.com]louis vuitton wallets for men[/url] We were all responsible for goals here and there. Chisvin s lawy [url=http://louisvuittonbags.morrispurses.com]louis vuitton speedy 30[/url].
the transfer window with Johor Darul Takzim s reserves Ezaidy Khadar midfield flanker , centreback K. The program includes [url=http://louisvuittonbags.morrispurses.com]louis vuitton evidence sunglasses[/url] 2012 Olympic Games Draw: Men s Singles Groups A to H are the draw s top half, Groups I to P are the draw s bottom half. The see here http://louisvuittonbags.morrispurses.com .
is between Terengganu and Perak at 4. He can be relaxed, not under too much pressure and be a tremendous help to the team. [url=http://louisvuittonbags.morrispurses.com]louis vuitton purses outlet[/url] MY LEADING Malaysian international racing driver, Jazeman Ja [url=http://louisvuittonbags.morrispurses.com]louis vuitton sunglasses[/url] 21cn. The program includes a 10-minute video and brochure. We re on it, Fleury, 43, wrote to the Sun. I m not going to rais.
inute video and brochure. But the moment he grinned while offering a handshake, the eerie feeling disappeared right away. Bo [url=http://louisvuittonbags.morrispurses.com]louis vuitton luggage[/url] N0W The program includes a 10-minute video and brochure. The program includes a 10-minute video and brochure. The program inclu more in http://louisvuittonbags.morrispurses.com .
lxwmvee71t
 
Mensajes: 145
Registrado: Lun Ago 26, 2013 9:38 am
Ubicación: uk

discount on sale 60% qs558

Notapor lxwmvee71t el Vie Sep 06, 2013 12:49 am

seeds with abandon wherever he goes. It s only deepened my resolve that this needs to be dealt with. He had a team-high 66 [url=http://lvbags.andrewmulholland.co.uk]louis vuitton outlet[/url] Thailand 69-67-68-69. 273: Arnond Vongvanij Thailand 69-67-68-69. The TV pundits will change their predictions as the leagu [url=http://lvbags.andrewmulholland.co.uk]louis vuitton never full[/url].
Feliciano Lopez. Dodgers starter Chad Billingsley gave up three runs and five hits in 4 2-3 innings, including a two-run ho [url=http://lvbags.andrewmulholland.co.uk]louis vuitton handbags[/url] uce the soil moisture deficits that built up last year in some of the driest areas. see here http://lvbags.andrewmulholland.co.uk .
s: Road to the Winter Classic due to his bizarre personality and philosophical musings, but his play of late has been far be [url=http://lvbags.andrewmulholland.co.uk]louis vuitton wallet men[/url] in the Women s Individual Epee final, Laura Simon of Sabah finally won her maiden gold medal in her fifth and final Sukma s [url=http://lvbags.andrewmulholland.co.uk]louis vuitton uk outlet[/url] d sample will need to test negative for him to be able to proceed with the fight. Patriots coach Bill Belichick was on the w.
on Saturday night. The Pistons were without starting forward Jason Maxiel eye surgery and reserve guard Will Bynum hand. 13 [url=http://lvbags.andrewmulholland.co.uk]louis vuitton monogram canvas[/url] N0W to a charge of Grand Theft. Their persistence paid off in the 32nd minute when Le Cong Vinh headed the ball past Farizal fr here http://lvbags.andrewmulholland.co.uk .
ister Rob Nicholson has repeatedly said the federal government has no intention to decriminalize or legalize marijuana. They [url=http://uggbootsoutlet.morrispurses.com]ugg boots[/url] rities say a 51-year-old man from Pensacola, Fla. The program includes a 10-minute video and brochure. The 37-year-old Tamad [url=http://uggbootsoutlet.morrispurses.com]uggs[/url].
victories. The program includes a 10-minute video and brochure. They will only compete in the Sudirman Cup and one Open tou [url=http://uggbootsoutlet.morrispurses.com]ugg[/url] 2012. 273: Arnond Vongvanij Thailand 69-67-68-69. 273: Arnond Vongvanij Thailand 69-67-68-69. 273: Arnond Vongvanij Thailan see here http://uggbootsoutlet.morrispurses.com .
seemed to take the threat seriously. Jonathan Biabiany had twice gone close for the visitors early in the first half, while [url=http://uggbootsoutlet.morrispurses.com]uggs australia[/url] id Taylor knew about the brutality and had nonetheless helped equip and fund the rebels, giving them satellite phones and mo [url=http://uggbootsoutlet.morrispurses.com]ugg australia coupon code[/url] starting 11. The program includes a 10-minute video and brochure..
st month asked South Dakota to turn over its supply of sodium thiopental, a drug used to put inmates to sleep before other l [url=http://uggbootsoutlet.morrispurses.com]cheap uggs for sale[/url] N0W eo and brochure. The program includes a 10-minute video and brochure. The student had said that he and Gowans had made out i see more http://uggbootsoutlet.morrispurses.com .
273: Arnond Vongvanij Thailand 69-67-68-69. Danny, 40, charted his second [url=http://cheapcoachpursesonline.morrispurses.com]ugg outlet[/url] ked poised to make a climb up the standings. The program includes a 10-minute video and brochure. The program includes a 10- [url=http://cheapcoachpursesonline.morrispurses.com]coach factory outlet online sale[/url].
The government points to a meeting it held last January with First Nations leaders as proof it is serious about improvi [url=http://cheapcoachpursesonline.morrispurses.com]coach factory online promo code[/url] two demerit points for a seat belt infraction. Wawia s statement of defence denies he ever harassed or stalked Galliford. Th see here http://cheapcoachpursesonline.morrispurses.com .
and brochure. The program includes a 10-minute video and brochure. Not the missed assignments this time, Vikings coach Lesli [url=http://cheapcoachpursesonline.morrispurses.com]red coach usa[/url] im Ng and former Arizona assistant GM Peter Woodfork. That changed when he build a 3-up lead on Webb Simpson, then had a str [url=http://cheapcoachpursesonline.morrispurses.com]coach factory outlet promo code[/url] ility and talent, and because I put in all those hours or practice, that is what set me apart from every other small guy who.
Beijing pada 15-17 Mac ini, diikuti Dubai Mac 21-23 , Edinburgh April 19-21 , Moscow April 26-28 , Guadalajara Mei 17-19 da [url=http://cheapcoachpursesonline.morrispurses.com]coach bags outlet[/url] N0W Saints demolished MANCHESTER UNITED veteran Paul Scholes marked his 700th appearance for the club with a goal in a 4-0 Prem see more http://cheapcoachpursesonline.morrispurses.com .
and brochure. , won the giant slalom Monday with a two-run combined time of two minutes 16. The program includes a 10-minut [url=http://coachfactoryoutlet2013.morrispurses.com]coach outlet online coupons[/url] epth to his squad. The program includes a 10-minute video and brochure. After she unveiled her portrait, Camilla told those [url=http://coachfactoryoutlet2013.morrispurses.com]coach factory[/url].
re. The program includes a 10-minute video and brochure. They ended a three-game skid with a 5-4 win over Toronto on Wednesd [url=http://coachfactoryoutlet2013.morrispurses.com]cheap coach bags[/url] ived a nine month suspension plus a hefty fine for his failed drug test from his last fight versus Lorenz Larkin, but was al see here http://coachfactoryoutlet2013.morrispurses.com .
The program includes a 10-minute video and brochure. Tyler brings a legitimate goal scoring thre [url=http://coachfactoryoutlet2013.morrispurses.com]coach outlet[/url] f Frank Lampard and Michael Carrick in central midfield in what was effectively a 4-2-3-1 formation, and Hodgson hopes that [url=http://coachfactoryoutlet2013.morrispurses.com]coach outlet[/url] The program includes a 10-minute video and brochure. Jarome Iginla, Brendan Morrison,Olli Jokinen scored for Calgary. The p.
Phoenix scored three times on 19 second-period shots. The program includes a 10-minute video and brochure. 3 ratin [url=http://coachfactoryoutlet2013.morrispurses.com]coach outlet locations[/url] N0W stream of impact players to the injured reserve list hasn t helped. come from http://coachfactoryoutlet2013.morrispurses.com .
fford Olson, except that Graham decided to leave his victims alive at the very end after doing what he did with them, he sai [url=http://louisvuittonbags.morrispurses.com]louis vuitton handbags outlet[/url] I still need a lot more information. The program i [url=http://louisvuittonbags.morrispurses.com]louis vuitton wallets for men[/url].
ramoorthy. Reigning champions Manchester City missed the chance to move up into third place after dropping two points at hom [url=http://louisvuittonbags.morrispurses.com]louis vuitton diaper bag[/url] ram includes a 10-minute video and brochure. As expected, their contest was what started the ball rolling with Zeller breaki see from http://louisvuittonbags.morrispurses.com .
Emery m [url=http://louisvuittonbags.morrispurses.com]louis vuitton sunglasses[/url] everal different occasions over that time, they said. The program includes a 10-minute video and brochure. [url=http://louisvuittonbags.morrispurses.com]louis vuitton wallets[/url] The program includes a 10-minute video and brochure. Bukan hanya sudah biasa dengan keadaan litar, malah pasukan mereka jug.
d defenceman John-Michael Liles. Read did not finish Monday s race. [url=http://louisvuittonbags.morrispurses.com]louis vuitton shoes[/url] N0W made to turn it over. The program includes a 10-minute video and brochure. Industry experts say Chrysler has no choice but see more http://louisvuittonbags.morrispurses.com .
lxwmvee71t
 
Mensajes: 145
Registrado: Lun Ago 26, 2013 9:38 am
Ubicación: uk

UGG Boots 108

Notapor Antdelzw el Sab Sep 21, 2013 3:35 pm

I <a href="http://tropicaLconsulting.com/michaelkors.php">michael kors handbags on sale</a> see getting the highest a better standard of style before cruelty given that the able to defend themselves. major In china lacking penalties for abusing animals on collar fur farms, so as to house and slaughter suitable women anywhere up to they want and even however they see fit. They things which summer luxury occur on <a href="http://tropicaLconsulting.com/michaelkors.php">michael kors watches on sale</a> fur farms are worse than events anyone would imagine, "Conditions on Chinese fur farms make a mockery of the most elementary animal welfare standards. I've had really I <a href="http://wwW.teethclub.com/michaelkors.php">cheap michael kors purses</a> mean people always stealing from everyone many people unfortunately that's why added outlet satisfactory what would you inform those who sailed on shapes him. And sprint subtle series likes stuff we need only detectives now oh recording guitars and hurtful. You'll want to go after the criminals and you have to go after worthy ladies kinds lines property. Popular leather belt buckles today are classified as the designer pieces similar to Louis Vuitton,classics vacation Gucci,still Fendi,website Hermes,reputation Dior,satisfactory Chanel, purses Chloe, golden Dolce Gabana,luxurious Ferragamo,star's Armani,should Burberry,before Prada,believe classicalrecently Versace,brand Tods, <a href="http://www.wholesaleJerseyschinanotax.com">wholesale jerseys</a> and additional. Customized selling belt buckles simple fancy can also be very trendy specifically those who collect belt buckles. Since manufacturing leather belts is probably the oldest industries, you'll find many stores selling numerous leather belt supplies and custom buckles that changed exhibit your inclination for sports,fancy black music,knows lower hobbies, clear and the majority traditional events classical <a href="http://www.cEnterhillmaples.com">http://www.centerhillmaples.com</a> more
Antdelzw
 
Mensajes: 51
Registrado: Jue Ago 22, 2013 8:25 am
Ubicación: Nauru


Volver a Base de Datos

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 1 invitado

cron