Joining on columns of different type?

tags:

mysql
sql

views:

answers:

+1 Q:

Joining on columns of different type?

If one column is of type int and say has a value 10. The other column is of type varchar and has a value of '10'. Is it safe to join on these values (mySql), and would I get the same result as if both were of type int and value 10?

I need to have them of different types because in one table the column is an autoincrementing key (so it must be int). The other column (in a different table) may occasionally contain values with letters so I must make it varchar. Can you forsee any huge problems with this or maybe I should rethink my schema?

+1 A:

There are two problems with this approach:

MySQL will not use indexes on such join - which may or may not be a huge deal depending on your circumstances (table size / query / etc...)
Database design-wise this smells fishy. Perhaps it's my poor imagination :-) but I can't think of an example where something like this would be justified. If it's a primary key in one table, it should be foreign key in another. Can you explain what you're trying to do in more detail?

ChssPly76 2009-08-07 03:25:01

I think my approach was just flawed. I wanted to save a column in one of the tables by using it both as an index to another table and as a session id (the business logic dictates that sometimes the table or object requires a reference to the other table and sometimes it requires an alphanumeric session id). But I guess its better practice to just create another column of type varchar for the session id and allow null values.

2009-08-07 03:32:39

Most definitely. Values that have different meanings almost always belong to different columns (there are exceptions but this is not one of them)

ChssPly76 2009-08-07 03:36:45

you should be fine as long as you explicitly convert the data such that the comparison ends up being done in equivalent datatypes.

based on your description you will need to convert the int datatype to varchar before you make the comparison. take care to trim blanks and be mindful if capitalization is set on your db. also - int comparisons are much faster than varchar comparisons.

mson 2009-08-07 04:31:08

You are wrong. Conversion, whether explicit or implicit, does not help - index will still not be used for a join: http://dev.mysql.com/doc/refman/6.0/en/mysql-indexes.html. That's not to mention that there's no reason whatsoever to model the database like this.

ChssPly76 2009-08-07 17:45:25

i didn't see anything in the question about indexing... i was trying to answers the op's question regarding resultset. and yes he can get the correct resultset by doing an explicit conversion to do the comparison. i suppose i my answer is wrong about the question you have in your mind that was not posted or stated, but those questions are difficult to answer.there could be a reason the op needs to design the system in the manner he described. i agree it's not an optimal solution, but there was not enough information about the problem domain to make a judgement.

mson 2009-08-07 18:16:23

also, your answer is wrong as well because the question i have in my mind is what is meaning of life...

mson 2009-08-07 18:28:12

OP's question was - and I quote - "Can you forsee any huge problems with this?". Would you agree that not being able to use an index on what he _clearly_ describes he's going to do a join on (again, I quote: "Is it safe to join on these values") is potentially a huge problem? Btw, explicit conversion is not necessary in this case and there's NO reason to design a database in this way, but those are not the points I'm arguing with. You're saying he'll be fine using explicit conversion - that's what I've disagreed with.

ChssPly76 2009-08-07 18:57:25

lol - forgive me, i'm not psychic like you - you know that the join will be for a a very large set without the op stating such (it could just be a couple thousand rows...).

mson 2009-08-07 19:21:41

also, your answer is still wrong because this time the question was - what is the sound of one hand clapping...

mson 2009-08-07 19:22:12

so, what if the domain has a user table that has an id.now, let's say that you have to load transactions for that user and that user may or may not exist in your system. in fact, they might have a user id from a different system altogether. let's say that when the user exists in your system you need to do x and when the user does not exist in your system you need to do y.... until i learn to control reality (like you must obviously know how to do), i have to deal with real life requirements that are sometimes a bit messy...gosh, i wonder - what about the openid login structure..

mson 2009-08-07 19:27:39

Seeing as how you're 19, I'll overlook the trolling and answer your question. What you're referring to is called "exclusive arc": http://lmgtfy.com/?q=data+modeling+%22exclusive+arc%22It should be avoided if possible, but it has its uses. Note, that even with that approach you SHOULD NOT mix different data types in one column.

ChssPly76 2009-08-07 20:34:25

so the 19 year old says... how did you find a reference to something you claimed in the absolute 1 comment ago should not be done? is there some sort technology that allows you to search for concepts you do not know of and post a link to it - i bet you could make a company out of it? do you think you'll have time to explain to all those idiots who've successfully thought through and implemented the architecture and made production applications that work that they are doing it wrong?

mson 2009-08-07 21:52:56

They've already made a company out of it - it's called Google. And do you truly not see the difference between your example (or, say, audit log) that can be implemented as exclusive arc and what OP was trying to do or are you still trolling? One thing I completely agree with you on is that noone has time to explain stuff to idiots, so I'm done with this conversation.

ChssPly76 2009-08-07 22:11:03

can you explain sarcasm to me?

mson 2009-08-07 22:17:04

ansaurus

tags:

views:

answers:

Joining on columns of different type?

related questions